first occurance of a value - 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

SQL Server 2000 Forums

SQL Server Development (2000)

first occurance of a value

Author

Topic

curious
Starting Member

8 Posts

Posted - 2009-01-27 : 10:06:46

I'm hoping someone could help me with my query.
I have a table 'TV_Sales' with 3 columns

refnum brand SaleDate
5 Sony 10/12/2008
5 Panasonic 10/13/2008
5 Samsung 10/14/2008
8 Sony 10/18/2008
8 Sharp 10/22/2008

I need to have a query that results in

5 Sony 10/12/2008
8 Sony 10/18/2008

which is the first sales date of each refnum.

my query:

select refnum, Min (SaleDate)
from TV_Sales
Group By refnum

I get

5 10/12/2008
8 10/18/2008

however if I add brand to my query I get all the records. Any suggestions would be appreciated.

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-01-27 : 10:17:29

many ways to do this in one select but its good to start thinking these things through.

OK so your half way there.
You have a select that is isolating the key to the records needed for your final results set.

Think about building another select around that select.
or putting your results into a temporary table and writing another select using the in clause.
or maybe you can write a self join comparing dates only returning the records to match your requirement.

curious
Starting Member

8 Posts

Posted - 2009-01-27 : 10:31:35

quote:
Originally posted by curious

I'm hoping someone could help me with my query.
I have a table 'TV_Sales' with 3 columns

refnum brand SaleDate
5 Sony 10/12/2008
5 Panasonic 10/13/2008
5 Samsung 10/14/2008
8 Sony 10/18/2008
8 Sharp 10/22/2008

I need to have a query that results in

5 Sony 10/12/2008
8 Sony 10/18/2008

which is the first sales date of each refnum.

my query:

select refnum, Min (SaleDate)
from TV_Sales
Group By refnum

I get

5 10/12/2008
8 10/18/2008

however if I add brand to my query I get all the records. Any suggestions would be appreciated.

I have modified my query and still not the result that I need

select * from TV_Sales where
SaleDate in (select Min(SaleDate) from TV_Sales)

gives me only the first record and if I change my quert to

select * from TV_Sales where
SaleDate in (select refnum, Min(SaleDate) from TV_Sales group by
refnum)

I get an error.
??????????

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 10:43:02

[code]Select t.refnum,t.brand,t.SaleDate
from table t inner join
(Select refnum,Min(SaleDate)as MinSale
from table
Group by refnum)m
on t.refnum = m.refnum
and t.SaleDate = m.MinSale
order by refnum[/code]

curious
Starting Member

8 Posts

Posted - 2009-01-27 : 10:51:35

quote:

Originally posted by sodeep

Select t.refnum,t.brand,t.SaleDate
from table t inner join
(Select refnum,Min(SaleDate)as MinSale
 from table
 Group by refnum)m
on t.refnum = m.refnum
and t.SaleDate = m.MinSale
order by refnum

Thanks sodeep, it worked

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-01-27 : 11:09:21

Some unsolicited advice; SoDeep did not have any question whether or not his solution would work and good for you, now you have your solution but try not to move on quite yet. Learning is a lot of fun but sometimes frustrating too but we all do it every day. Look up the error you received from your attempt and file it away for next time, learn the rules of SQL and apply them to different scenarios. Try to write more than one solution to a problem and figure out what makes them the same or different. The information shared here in the forums is for you to become better and not only will you reap the benefits but as well will you gain respect from the community. … I usually just read posts but I am feeling inspired today.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources