Select Query - Top 1 Selection - SqlServer2008

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

Select Query - Top 1 Selection - SqlServer2008

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2010-06-29 : 06:42:09

Hi,
I have a clarification with top1 selection

SuplierMst
---------------------
Sup_code,Sup_Name
----------------------
AA1, ABC CORPORATION
AA2, BCD TRADINGS
AA3, CODEPROJECT

InvoiceMst
---------------------------------------------------
Invoice_No,Invoice_Dt,Sup_Code,Unit_Rate,Item_Code
---------------------------------------------------
291, 06/24/2010, AA2, 75.00, JUG1
292, 06/25/2010, AA1, 65.00, JUG1
293, 06/25/2010, AA2, 73.00, JUG1
294, 06/26/2010, AA1, 61.00, JUG1
295, 06/27/2010, AA2, 80.00, JUG1

My required O/P is by last entry to Sup_code from Invoice_dt

AA1, 294, 06/26/2010, 61.00
AA2, 295, 06/27/2010, 80.00

My Query:

Select S.Sup_code,I.Invoice_no,I.Invoice_dt,I.unit_rate from SuplierMst s, InvoiceMst I where top1………………..?

Thanks

Paramu @ PARANTHAMAN

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 06:55:31

No need to use Top 1 just use row_number as follows -


SELECT * FROM 
(
	Select	S.Sup_code,I.Invoice_no,I.Invoice_dt,I.unit_rate, 
		ROW_NUMBER() OVER ( Partition BY S.Sup_Code ORDER BY I.Invoice_dt DESC ) RowNo 
	from	SuplierMst s, InvoiceMst I
) T WHERE RowNo = 1

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-06-29 : 06:58:45

No need for using TOP 1 .Try this


select * from
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid 
from InvoiceMst

)T where rid=1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:03:50

quote:
Originally posted by Idera

No need for using TOP 1 .Try this

select * from
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid
from InvoiceMst

)T where rid=1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Same solution...

Great minds think alike...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-06-29 : 07:19:02

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by Idera

No need for using TOP 1 .Try this

select * from
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid
from InvoiceMst

)T where rid=1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Same solution...
Great minds think alike...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Damn dont know what is happening.I am missing other peoples replies.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146620#575745

quote:

Great minds think alike...

Well I am not quite sure whether I have a great mind

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 07:22:19

Solutions are ok but if the OP has to join the first table because he needs the SUP_NAME then he should join the derived table.
select sm.SUP_NAME, dt.Invoice_no,dt.Invoice_dt,dt.unit_rate
from SuplierMst sm
join
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid
from InvoiceMst
)dt
on sm.sup_code = dt.sup_code and dt.rid=1

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:28:13

quote:
Originally posted by webfred

Solutions are ok but if the OP has to join the first table because he needs the SUP_NAME then he should join the derived table.
select sm.SUP_NAME, dt.Invoice_no,dt.Invoice_dt,dt.unit_rate
from SuplierMst sm
join
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid
from InvoiceMst
)dt
on sm.sup_code = dt.sup_code and dt.rid=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Well I am not able to see Sup_Name in Original post...
Can you please point out...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:28:31

quote:

Well I am not quite sure whether I have a great mind

But i m sure about you because you are thinking like me

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

paramu
Posting Yak Master

151 Posts

Posted - 2010-06-29 : 07:28:33

WonderFul! Both replies are powerful!
Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 07:33:25

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by webfred

Solutions are ok but if the OP has to join the first table because he needs the SUP_NAME then he should join the derived table.
select sm.SUP_NAME, dt.Invoice_no,dt.Invoice_dt,dt.unit_rate
from SuplierMst sm
join
(
select *,
row_number()over(partition by Sup_Code order by Invoice_Dt desc)as rid
from InvoiceMst
)dt
on sm.sup_code = dt.sup_code and dt.rid=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Well I am not able to see Sup_Name in Original post...
Can you please point out...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Don't worry. It was just a guess because the OP has shown both tables and a select statement with a join...

You are right when you say that the wanted output like shown in first post needs no sup_name

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources