Query to get 'current order' from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to get 'current order' from a table

Author

Topic

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-09 : 15:46:16

table1
col1,col2, col3..,col4
A1, A2, A3.., 1
A1, A2, A4.., 1
A1, A3, A4.., 2
.. ... .... .....

for this record I want to get the 'current order'

I can do it like this

Select col1, max(col4) col4
into temp_table
from table1
group by col1

Select a.* from table1 a , temp_table t1
where a.col1 = t1.col1
AND a.col4 = t1.col4

Can I do this in a single select statement.. if so how

Thanks

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-09 : 16:02:17

This article goes into detail of what you need to do to get rid of the temp table:
http://www.sqlteam.com/item.asp?ItemID=6692

bm1000
Starting Member

37 Posts

Posted - 2002-01-09 : 16:05:50

/* use a correlated query. note the alias (a) */

select * from table1 a
where col4 = (select max(col4) from table 1
where col1 = a.col1)

Nazim
A custom title

1408 Posts

Posted - 2002-01-10 : 00:27:13

How about this one.

Select distinct top 1 * from table1 order by col4 desc

or

Select distinct * from table1
where col4= (select max(col4) from table1)

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-10 : 07:30:26

Thank You very much guys...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources