Select top 1 c1 from a select statement

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 2005 Forums

Transact-SQL (2005)

Select top 1 c1 from a select statement

Author

Topic

torlok2002
Starting Member

11 Posts

Posted - 2008-12-31 : 15:00:05

Here's what my data looks like

Partno, jobno, date, etc...
1 101

torlok2002
Starting Member

11 Posts

Posted - 2008-12-31 : 15:07:53

Ok, accidentally posted, whoops. Let me finish

Data looks like
======================
Partno, jobno, date, etc...
1, 101,
2, 102
1, 103,
2, 104,
1, 105,
1, 106,

What I want would be the first partno (determined by earliest date, not the min(partno)). I also want the Job to be listed in the output as well as some other columns.

If I do:
SELECT MIN(fpartno) AS Part_No
FROM jomast
GROUP BY fpartno

I get a list of all the fpartno's, but no way to tie into the first instance of a job determined by date.

Any help would be greatly appreciated. I'm no master but slowly learning. :)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-31 : 15:16:05

SELECT a.PartNo,a.JobNo
FROM
jomast a
INNER JOIN
(select [date] = min(date) from jomast)b
ON
a.[Date] = b.[date]
Jim

torlok2002
Starting Member

11 Posts

Posted - 2008-12-31 : 16:03:55

Thank you Jimf, but this just gives me one entry, which is the same as the entry with the oldest date...

This is what I've translated it to in case I made a mistake on my part.

SELECT a.fpartno, a.fjobno
FROM jomast a INNER JOIN
(SELECT [fddue_date] = MIN(fddue_date)
FROM jomast) b ON a.fddue_date = b.fddue_date

Whats I'm looking for would be this, but I want all the data for a the row that the first fddue_date for a fpartno.

There are multiple jobs for each Part, and dates for those in the same row. Any ideas?

Thiyagu_04
Starting Member

37 Posts

Posted - 2009-01-01 : 02:06:56

can u tell.how ur result will look like

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:22:23

[code]
SELECT partno,jobno, date
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY partno ORDER BY date) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-02 : 02:10:54

See what you can do with row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

torlok2002
Starting Member

11 Posts

Posted - 2009-01-05 : 08:14:02

Well, Looks like I'm on SQL2000, not 2005. Row_number is not available I'm guessing? It looks exactly like what I need. Any other suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:49:07

quote:
Originally posted by torlok2002

Well, Looks like I'm on SQL2000, not 2005. Row_number is not available I'm guessing? It looks exactly like what I need. Any other suggestions?


SELECT *
FROM
(
SELECT (SELECT COUNT(*) FROM Table WHERE partno=t.partno AND date<=t.date) AS Seq,*
FROM Table t
)r
WHERE Seq=1

torlok2002
Starting Member

11 Posts

Posted - 2009-01-05 : 15:50:30

visakh16, that worked, however I'm looking to make a change. If I understand how this is working through the Count, logic and I want to add another field that would show up as unique (and therefore index as 1). A part revision(fpartrev)

This is what I've got:

SELECT seq, fjobno, fpartno, fpartrev, fddue_date, fcompany, fcus_id
FROM (SELECT (SELECT COUNT(*)
FROM jomast
WHERE fpartno = t .fpartno AND fpartrev = t .fpartrev AND fddue_date <= t .fddue_date) AS seq, *
FROM jomast t) r
WHERE (seq = 1) AND (fddue_date > ' 12 / 31 / 07 ') AND (fddue_date < ' 1 / 1 / 2009 ') AND (fcompany = 'COMPANY1')
ORDER BY fddue_date

See any problems? If I understand correctly it's looking for all matching fpartno's and looking for the earliest date and counting up as dates increment? If I add a partrev in there, how does it effect the output? Does it matter if where I put it in the count statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 03:33:35

then it will look for combination fpartno AND fpartrev and number sequence within each group of them as 1,2,3...

torlok2002
Starting Member

11 Posts

Posted - 2009-01-06 : 09:42:05

Thanks All. This worked like a charm (I also learned much about nested select statements!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 09:43:01

welcome