getting the most latest date

I have a column which stores dates (datetime data type). I would like
to fetch one data which is the most latest date among them. So if there
are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
need to output.

how can I write this in sql stmt?
select datebegin
from testtable
where datebegin = ???

I don't think it is simple as I thought. I have no idea what I need to
write in where clause to make this work.
thanks.select MAX(datebegin) from testtable

TGEAR wrote:
> I have a column which stores dates (datetime data type). I would like
> to fetch one data which is the most latest date among them. So if there
> are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
> need to output.
> how can I write this in sql stmt?
> select datebegin
> from testtable
> where datebegin = ???
> I don't think it is simple as I thought. I have no idea what I need to
> write in where clause to make this work.
> thanks.sql

Getting the latest row from a batch

Hi All

This is a belter that my little brain can't handle.

Basically I have 1 SQL table that contains the following fields:

Stock Code
Stock Desc
Reference
Transaction Date
Qty
Cost Price

Basically this table stores all the transaction lines of when a user
books stock items into stock so that they can look at a journal of
this goods in as and when they please.

My task is that the user wants a list of all the stock items with the
last cost price that they were booked in at.

So I think I have to find the last transaction date used for each
stock code and then bring this back as 1 row per stock code with the
above fields of data.

How the whats-its can I do this? Is it acutally possible?

Any help you can give is much appreciated.

Rgds

LaphanSELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks David

Very much appreciated.

Rgds

Laphan

David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nJmdnSNww5bmVkmi4p2dnA@.giganews.com...
SELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the help David. The below script works perfectly
apart from the fact that I need to add a cost price field from another
table:

SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
FROM STOCKTRANSACTIONS AS S1
JOIN
(SELECT STOCKID, MAX(TRANSACTIONDATE)
FROM STOCKTRANSACTIONS
WHERE TRANSACTIONTYPE = 3
GROUP BY STOCKID)
AS S2(STOCKID, TRANSACTIONDATE)
ON S1.STOCKID = S2.STOCKID
AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

Could you please let me know how I can add an additional COSTPRICE
column from a STOCKPRICES table to this script.

FYR, if I was to perform a straightforward query to join the
STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
would look to get the required data, but this wouldn't contain the new
fangled 'find last date' thing that you sent me:

SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
FROM STOCKPRICES, STOCKTRANSACTIONS
WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
(STOCKPRICES.PRICELEVELID='1'))

Any ideas on how to sync these 2 queries??

Rgds

Laphan

Getting The Latest Record

Hello Everyone,

I would like advise on what I am doing. I am trying to get the maximum date per record from a table. It is a very simple query but I am wondering why I am not getting the right result. Please look at the following query:

select account_id, amount, max(tran_date) from evalucheck_history group by account_id, check_amount order by account_id

This query is supposed to give me the record with the latest date but instead I get the following (snap shot of the result):

Account ID Amt Date
9999999999000100 174.8 1-Dec-2006
9999999999000100 223.69 25-Oct-2006
9999999999000100 358.5 9-Nov-2006
9999999999000100 393.5 14-Nov-2006
9999999999000100 441.98 24-Oct-2006
9999999999000100 476.93 20-Oct-2006
9999999999000100 552.07 10-Jan-2007
9999999999000100 627.23 2-Nov-2006
9999999999000100 705.94 19-Oct-2006
9999999999000100 713.61 4-Dec-2006
9999999999000100 729.71 30-Oct-2006
9999999999000100 747.24 13-Mar-2007
9999999999000100 998.97 19-Apr-2007

Can you please help me on this?

Thank you all.

RandyI think the lack of a WHERE clause is why this isn't giving you what you want.
Here's a quick guess

SELECT account_id
,amount
,tran_date
FROM evalucheck_history
WHERE tran_date =
(
SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id
)
ORDER BY account_id|||um, george, what is that funky "x dot" notation supposed to be doing?

... FROM x.evalucheck_history As x|||SELECT account_id
, amount
, tran_date
FROM evalucheck_history as X
WHERE tran_date =
( SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = X.account_id )
ORDER
BY account_id|||EDIT: Typo :)
Isn't yours the same as mine..?
(Except we aliased differently)|||test 'em and see :cool:|||Thank you for your help guys. I appreciate it. I had the problem sorted out.

Sincerely,

Randy|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!maybe start a new thread? and show us your test data...|||No need for a new thread really?
I'm happy to bolt on here :p

SELECT x.*
FROM career As x
WHERE x.career_date =
(
SELECT Max(career_date)
FROM career
WHERE parent_identifier = x.parent_identifier
)

SELECT *
FROM career
WHERE career_date =
(
SELECT Max(x.career_date)
FROM career As x
WHERE parent_identifier = x.parent_identifier
)

(46223 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

Interesting, no?|||1 row, eh

:)|||I don't really get why though... I thought I had it a minute ago but other logic told me not to be stupid :p|||which row is it? my money is on the MAX(career_date) in the table ;)|||I thought that was obvious ;)
I just can't explain why!|||Your original alias was inside the subquery, which mean any testing in the subquery was NOT dependent on the outer query... thus

SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id

is the same as

SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = account_id

As you can see account_id = account_id for every row. You may as well have left off the WHERE clause there because it'll give you the same result.

Thus, it returns one result only which is ALWAYS the max tran_date from the table, which will only every match one row in your outer query.

hope that helps ;)|||In summation your subquery should have been dependent, but it wasn't ...|||aschk, nice analysis

you should take up writing, you're good at it

:)|||Aha - now I see!
Thank you aschk and Rudy.

Oh and yes - very complete and concise answers - Rudy's suggestion is a good 'un!|||Thanks rudy, a very flattering comment. I probably wouldn't consider writing to be my best skill, but I do like analysing work and trying to better explain it (providing I understand it) ;)

getting the latest datetime

Hi

I have a DateTime column in my table, and I want to select the first couple of rows whose DateTime values are closest to the currect serve rdatetime. Is there a function like Max() that I can use?

Also, quesion about timestamp. If I create a column of type timestamp, is the value automatically entered by the database on every insert entry to the table?
I have had problems using timestamp so I switched to using datetime now.

Help appreciated!For the first part of your question, I would use the DATEDIFF() function to find the difference between the current date (GETDATE()) and the date value stored in your column, sorted in DESC order, taking the TOP 1 or 2 (however many you want).

The Timestamp data type is actually a unique rowversion containing binary/varbinary data, and its value only has meaning to SQL Server itself. SQL Server updates the value in this column upon every INSERT, UPDATE, and DELETE. You could make use of this Timestamp value for concurrency control, but it does not contain datetime data -- it contains binary/varbinary data.

Terri

Getting the latest dated Row for each user in a table

Hi all,
I want to have a query that will return me a single row for each userfrom a table where the table has many rows for each user. The singlereturned row for each user must be the most recently dated entry([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.

select DISTINCT([I1-Customer-Ref]) AS Cust,
([i7-w-fixed-amnt]) as WaterFixedAmt,
([i7-w-rv-amnt]) as WaterRVAmt,
([i7-s-fixed-amnt]) as SewerageFixedAmt,
 ([I7-Change-Date]) AS [Date]
from r07UnMeasuredBills
ORDER BY Cust, [I7-Change-Date] DESC

I am using MS SQL Server 7 for this.
Thanks
Tryst
Have you tried aggregate functions?
i.e.
SELECT I1-Customer-Ref,Max(I7-Change-Date) FROM rO7UnMeasuredBills GROUP BY I1-Customer-Ref

|||

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT I7-Change-DATE FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||No, I haven't tried that.
Will MAX work on dates (DATETIME)?
(I can't check it now as I am not in work)
Tryst
|||

Yes, infact I forgot to actually do that in my previous post, bad me... It should have been

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT max(I7-Change-DATE) FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||Hi Motely, thanks for the reply.
Regarding the SQL query you provided, although it works (well, I hope, I haven't tried it yet :)), I am trying to get an understanding of how it works, but the sub-SELECT is confusing me a little. If your using the MAX function, won't that just bring back the row with the most recent date from all the rows and not for each user in the table? Would you be able to just break it down for me, so I can get an understanding of it?
Thanks once again.
Tryst
|||

It woul, however the table in the Sub Query is matched to the customer-ref in the where clause:

r.I1-Customer-Ref=r2.I1-Customer-Ref

Getting the LATEST DATE in the table

How to modify the below procedure in order to get the latest date ( the biggest value ) in the column "Deadline", type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer "UserName" ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE PROCEDURE GetLatestDate

(@.UserName VARCHAR(50)

AS

SELECT Deadline

FROM CustomerItems

WHERE UserName = @.UserName

try

select max(Deadline)from CustomerItemswhere UserName = @.UserName

|||Thank you,jdingo .sql

Getting records for latest 4 dates

Hi,
I would like to retrieve all the records in a table that are in the
latest 4 dates.
Each record has a date field. The latest date in the table may be today,
yesterday or last w - it varies.
I would like to be able to retrieve all records that belong to the
latest 4 dates in the table. Is it possible to do this in a stored
procedure?
Thanks,
DarenHi Daren
You may want to try something like:
CREATE TABLE #datedata ( dateval datetime, val varchar(10))
insert intO #datedata ( dateval, val )
select getdate(), 'a' as dateval
UNION ALL select getdate() -1, 'F'
UNION ALL select getdate() -2, 'b'
UNION ALL select getdate() -3, 'C'
UNION ALL select getdate() -4, 'E'
UNION ALL select getdate() -5, 'D'
SELECT * from #datedata
WHERE DATEVAL IN (
SELECT TOP 4 dateval FROM #datedata order by dateval DESC )
John
"Daren" <pearcy@.-removethis-gmail.com> wrote in message
news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
> Hi,
> I would like to retrieve all the records in a table that are in the latest
> 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the latest
> 4 dates in the table. Is it possible to do this in a stored procedure?
> Thanks,
> Daren|||Hi John,
I don't think this does what I'm after. I'm not looking to get all
records with dates in the last 4 days. I'm looking to get records with
dates in the last 4 days in the table.
Looking at the example table below, the SQL I'm after would retrieve
record ids 3999 to 3991 inclusive.
RecordID Date Added
3999 25-May-2005
3998 25-May-2005
3997 23-May-2005
3996 21-May-2005
3995 21-May-2005
3994 21-May-2005
3993 21-May-2005
3993 21-May-2005
3992 18-May-2005
3991 18-May-2005
3990 16-May-2005
3989 16-May-2005
3988 15-May-2005
Regards,
Daren
John Bell wrote:
> Hi Daren
> You may want to try something like:
>
> CREATE TABLE #datedata ( dateval datetime, val varchar(10))
> insert intO #datedata ( dateval, val )
> select getdate(), 'a' as dateval
> UNION ALL select getdate() -1, 'F'
> UNION ALL select getdate() -2, 'b'
> UNION ALL select getdate() -3, 'C'
> UNION ALL select getdate() -4, 'E'
> UNION ALL select getdate() -5, 'D'
> SELECT * from #datedata
> WHERE DATEVAL IN (
> SELECT TOP 4 dateval FROM #datedata order by dateval DESC )

> John
> "Daren" <pearcy@.-removethis-gmail.com> wrote in message
> news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
>
>
>|||This is why posting DDL and example data is important
(http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006 ) because it removes
this sort of ambiguity.
CREATE TABLE MyWork ( RecordID int, [Date Added] datetime )
INSERT INTO MyWork ( RecordID, [Date Added] )
SELECT 3999, '25-May-2005'
UNION ALL SELECT 3998, '25-May-2005'
UNION ALL SELECT 3997, '23-May-2005'
UNION ALL SELECT 3996, '21-May-2005'
UNION ALL SELECT 3995, '21-May-2005'
UNION ALL SELECT 3994, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3992, '18-May-2005'
UNION ALL SELECT 3991, '18-May-2005'
UNION ALL SELECT 3990, '16-May-2005'
UNION ALL SELECT 3989, '16-May-2005'
UNION ALL SELECT 3988, '15-May-2005'
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT TOP 4 [Date Added] FROM ( SELECT DISTINCT [Date Added] FROM
MyWork ) A order by [Date Added] DESC )
This also seems to work as TOP is applied after building the result
set.
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
DESC )=20
John|||John Bell wrote:
> This is why posting DDL and example data is important
> (http://www.aspfaq.com/etiquett_e.asp?id=5006 ) because it removes
> this sort of ambiguity.
>
Thanks for that advice, I'll try to remember it for future postings.

> This also seems to work as TOP is applied after building the result
> set.
> SELECT * from MyWork
> WHERE [Date Added] IN (
> SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
> DESC )
>
D'oh! Why didn't I think of that? Thanks, much appreciated!
Daren|||SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
"Daren" wrote:

> Hi,
> I would like to retrieve all the records in a table that are in the
> latest 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the
> latest 4 dates in the table. Is it possible to do this in a stored
> procedure?
> Thanks,
> Daren
>|||OOPS, you might need:
SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM (SELECT DISTINCT d FROM
tbl) a ORDER BY d DESC)
"Brian Selzer" wrote:
> SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
> "Daren" wrote:
>

Getting latest record from trigger

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

Quote:

Originally Posted by mccax

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

try getting the value from inserted/deleted tables

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
Paul G
Software engineer.
Hi
SELECT MAX(field1)
FROM table
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.
|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
>
>

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
--
Paul G
Software engineer.Hi
SELECT MAX(field1)
FROM table
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
>
>

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
--
Paul G
Software engineer.Hi
SELECT MAX(field1)
FROM table
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> > Hi I have a table like
> > *******************
> > *pri key * varchar (20)*
> > ********************
> > * 1 * tom *
> > * 2 * joe *
> > * 3 * paul *
> > Need a query that gets the highest pri key value. thanks.
> > mabye something like
> > SELECT field1
> > FROM table
> > WHERE field1 IS MAX
> > I did have a simple count but in working with the stored procedure that
> > writes to this table the pri key value got skipped for a few entries, so
> > count comes up short by a few.
> > --
> > Paul G
> > Software engineer.
>
>

Getting JDBC metadata for synonyms

Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.

Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.

Any Help would be appreciated!

Dave.

This a known issue and is being tracked by the SQL Server team. Can you describe the scenario for which you are usign synonyms? Are you able to owrk-around by using the table or view name?

|||Thanks for your reply,

The particular scenario we have is that we are developing a tool to create a Web Application from the database object definitions. We therefore, have no control over how the user has constructed his database and whether synonyms are used. We could get the data from the synonyms system table, but that would go against the design approach we have used. I will say that, unlike in Oracle, there seems to be no good reason to use synonyms in SQL Server at all, except possibly to enable the user to access different tables/views without changing code, which in my opinion, is a somewhat dubious practise anyway.

Dave

Getting JDBC metadata for synonyms

Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.

Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.

Any Help would be appreciated!

Dave.

This a known issue and is being tracked by the SQL Server team. Can you describe the scenario for which you are usign synonyms? Are you able to owrk-around by using the table or view name?

|||Thanks for your reply,

The particular scenario we have is that we are developing a tool to create a Web Application from the database object definitions. We therefore, have no control over how the user has constructed his database and whether synonyms are used. We could get the data from the synonyms system table, but that would go against the design approach we have used. I will say that, unlike in Oracle, there seems to be no good reason to use synonyms in SQL Server at all, except possibly to enable the user to access different tables/views without changing code, which in my opinion, is a somewhat dubious practise anyway.

Dave