Help With the MIN Function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help With the MIN Function

Author

Topic

ScottBouley
Starting Member

4 Posts

Posted - 2010-04-15 : 09:54:49

Please help. I need to narrow the select statement below so that only the record with the earliest DS_TS is returned. I know it involves using the MIN function but cannot figure out where to put it or the syntax to use. The data is in an Oracle database.

Thanks in advance.

SELECT EID, NUM_1, TYCOD, PRIORITY, DS_TS
FROM AEVEN
WHERE NUM_1='P1002437'

EID NUM_1 TYCOD PRIORITY DS_TS
155014 P1002437 LARCENY 2 20100122211023ES
155014 P1002437 LARCENY 2 20100122211023ES
155014 P1002437 LARCENY 2 20100122232223ES
155014 P1002437 LARCENY 2 20100122232223ES

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 10:09:39

[code]
select * from
(
select *,row_number()over(partition by EID order by DS_TS desc)as rid

)t where rid=1
[/code]
Since Oracle to have partition functions I have given you the solution.
This is an SQL server forum.Please post in oracle forum like this one http://www.orafaq.com

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 10:12:37

quote:
Originally posted by Idera

select * from
(
select *,row_number()over(partition by EID order by DS_TS)as rid

)t where rid=1

Since Oracle to have partition functions I have given you the solution.
This is an SQL server forum.Please post in oracle forum like this one http://www.orafaq.com

PBUH

ORACLE wont support * and specific columns. So * should be expanded to individual/required columns

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 10:24:09

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

select * from
(
select *,row_number()over(partition by EID order by DS_TS)as rid

)t where rid=1

Since Oracle to have partition functions I have given you the solution.
This is an SQL server forum.Please post in oracle forum like this one http://www.orafaq.com

PBUH

ORACLE wont support * and specific columns. So * should be expanded to individual/required columns

Madhivanan

Failing to plan is Planning to fail

Thanks for the info.

PBUH

ScottBouley
Starting Member

4 Posts

Posted - 2010-04-15 : 10:25:01

This may sound wierd but the interface I use to query the Oracle database seems to accept standard SQL statements. Can you help me with the SQL solution so I can try it?

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 10:27:24

Did you try the solution I gave you? Is it giving you any error?

PBUH

ScottBouley
Starting Member

4 Posts

Posted - 2010-04-15 : 10:41:33

I get FROM keyword not found where expected. Also I don't see the table name specified anywhere. Is what you posted the entire solution or do I need to enter the field and table names? I don't have any idea what I'm looking at. Sorry.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 10:45:19

select * from
(
select col1,col2,col3,row_number()over(partition by EID order by DS_TS desc)as rid from your_table

)t where rid=1

Madhivanan

Failing to plan is Planning to fail

ScottBouley
Starting Member

4 Posts

Posted - 2010-04-15 : 11:01:59

I entered:
SELECT * FROM (SELECT EID,NUM_1,DS_TS,ROW_NUMBER()OVER(PARTITION BY EID ORDER BY DS_TS DESC) AS RID FROM AEVEN)t WHERE RID=1 AND NUM_1='P1002437'

The query returned the first row but not the correct row. i CHANGED descending to ascending and received the correct row. Now all I need to to do is add a bunch more fields and a few calculations.

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources