Getting the top record - 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

Getting the top record

Author

Topic

karrojo
Starting Member

26 Posts

Posted - 2011-08-10 : 01:30:40

Good Day!

I have two tables. First table is Info_Table, Second: Checks_Table. I need to get the first check issued in the Checks Table. However, whenever i run my SQL command, i don't get any results. here is my command:

select Ref_NO, col1, col2, b.d_issue
from info_table a join
(select top 1 d_issue, ref_no from checks_table) b
on a.ref_no = b.ref_no

Is my query correct? I would really appreciate your help.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-10 : 02:22:14

TOP(1) according to what criteria?

N 56°04'39.26"
E 12°55'05.63"

karrojo
Starting Member

26 Posts

Posted - 2011-08-10 : 07:05:10

thanks swepeso for your time.

The Top 1 is to get the first check issued under the Checks_Table

I tried modifying the query by removing the top 1 and got all the checks under the checks_Table however when putting the Top 1 command, i got no results.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-10 : 07:18:48

Try this

select Ref_NO, col1, col2, b.d_issue
from info_table a join
(
select ref_no,ROW_NUMBER() over (order by d_isse) as sno from checks_table
) b
on a.ref_no = b.ref_no
where b.sno=1

Madhivanan

Failing to plan is Planning to fail

karrojo
Starting Member

26 Posts

Posted - 2011-08-10 : 21:33:25

thanks madhivanan! for your time

i got this error msg: Row_number is not a recognized name

i am using MS SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-10 : 23:36:35

select Ref_NO, col1, col2, b.d_issue
from info_table as a
inner join (select max(d_issue) as d_issue, ref_no from checks_table group by ref_no) as b on b.ref_no = a.ref_no

N 56°04'39.26"
E 12°55'05.63"

karrojo
Starting Member

26 Posts

Posted - 2011-08-11 : 00:53:34

thanks so much SwePeso! ^_^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources