SQL Query

[ttohme]

Hi guys,

I have 2 simple questions I'm sure.

How can I display all columns in a table of a specific record with a condition to show only the record with the maximum value:
ex:
RecordNumber RecordName RecordDate Value
1 Test Jan, 2000 20
3 Test2 Jan, 2001 19
1 Test3 Jan, 2002 21

so the query should return :
1 Test3 Jan,2002 21
What I want is to show the record with RecordNumber=1 and the one that has the highest value.



My Next question, using the same table as above, is there a query that returns the next available recordNumber, which is 2, instead of me iterating through the records trying to find the empty slot.

Thanks in advance for all your help.

[dinesh123]

SELECT *
FROM Table1 s1 INNER JOIN
(SELECT MAX(Value) AS Expr1
FROM Table1
WHERE (RecordNumber = 1)) s2 ON s1.RecordNumber = s2.Expr1

[ttohme]

Thanks a lot. It workeed like a charm.

How about my second question, is there a query that returns the next available slot ( RecordNumber=2 )?

Thanks again

[dinesh123]

U want the 2nd maximum
there is an article write by me in SQLSERVERCENTRL.COM

go to this

If it is hellpd I'll be glad if u rate it

http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp

[ttohme]

No not the second maximum, but let's say you have a set of records as folows:
(1,2,3,5,7,8,9,10), each one of those number represent a field in a table, and you want to get the first missing number in this set, how would you do it using a query?