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.
Author |
Topic |
Riesenrind
Starting Member
4 Posts |
Posted - 2013-01-13 : 05:13:20
|
Hi. I have a big dataset (1 million records) which i've been working with in excel. But its getting too slow, so i want to do the task with SQL.the dataset contains sales data and includes the fields State, productType, and Month. I need to get one record for each combination of these fields. In Excel, I created a calculated field as State&productType&Month and then deduplicated on that. I've done the easy bit:Select * from salesData where ...Can anyone help me with the where clause?Sql newbie |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2013-01-13 : 05:58:32
|
[code]SELECT DISTINCT [State],[productType],[Month]FROM salesData;[/code] |
|
|
Riesenrind
Starting Member
4 Posts |
Posted - 2013-01-13 : 12:19:38
|
Thanks. I forgot to mention, that there are lots of other fields, eg productId, productPrice, and I need them included in the output (but not regarded as DISTINCT). |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-13 : 12:23:49
|
[code]Select * from(Select *,ROW_NUMBER() OVER (PARTITION BY State, productType, Month Order by <Your Field>)as Seqfrom Table)Pwhere P.Seq = 1[/code] |
|
|
Riesenrind
Starting Member
4 Posts |
Posted - 2013-01-13 : 12:23:54
|
... A bit more explanation....I'm looking to get one example record for each combination of State, ProductType and Month, and I need all the other fields in that example record. |
|
|
Riesenrind
Starting Member
4 Posts |
Posted - 2013-01-13 : 12:25:15
|
Wow sodeep. That was really quick. That looks like exactly what I want. thanks! |
|
|
|
|
|
|
|