help on a query - 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 2012 Forums

Transact-SQL (2012)

help on a query

Author

Topic

russs
Starting Member

12 Posts

Posted - 2013-06-22 : 11:14:56

hello
i have table mytable with columns code, crea_date, and quantity

code | crea_date | quantity
------------------------------
1 | 1/1/2000 | 5
1 | 1/1/2000 | 6
2 | 1/2/2000 | 1
3 | 1/3/2000 | 2
4 | 1/2/2000 | 4
4 | 1/2/2000 | 3

i want a query (in pure sql) that returns only one row per couple (code, crea_date) regardless of quantity, in the example above, it should return :

code | crea_date | quantity
------------------------------
1 | 1/1/2000 | 5
2 | 1/2/2000 | 1
3 | 1/3/2000 | 2
4 | 1/2/2000 | 3

thank you in advance

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-23 : 01:02:05

hello
try this


hello
try this



;with myTable 
AS
(
	select 1 as code, '1/1/2000' as [crea_date] , 5 as [quantity]
	union all
	select 1 , '1/1/2000', 6
	union all 
	select 2 , '1/2/2000' , 1
	union all
	select 3 ,'1/3/2000', 2
	union all
	select 4 , '1/2/2000' , 4
	union all
	select 4 ,'1/2/2000',  3
)

SELECT * 
FROM
(
SELECT * 
,ROW_NUMBER() OVER(PARTITION BY code,[crea_date] 
 ORDER BY code,[crea_date],[quantity]) AS [RN]
FROM [myTable] )Q
WHERE Q.RN=1

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-23 : 01:03:36

[code]
code crea_date quantity RN
1 1/1/2000 5 1
2 1/2/2000 1 1
3 1/3/2000 2 1
4 1/2/2000 3 1

[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

russs
Starting Member

12 Posts

Posted - 2013-06-23 : 09:32:47

quote:
Originally posted by stepson

hello
try this

Thank you stepson, but is there a solution with standard sql ? (without the row() functions)

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-23 : 14:01:58

SELECT Code, Create_Data, MIN(Quantity) AS Qty FROM dbo.Table1 GROUP BY Code, Create_Date

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 00:56:22

quote:
Originally posted by russs

quote:
Originally posted by stepson

hello
try this

Thank you stepson, but is there a solution with standard sql ? (without the row() functions)

thank you

what do you mean by "standard sql"
All the posted solutions using standard functions of t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-24 : 02:30:10

Maybe OP is using Access?

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

russs
Starting Member

12 Posts

Posted - 2013-06-24 : 04:30:29

quote:
Originally posted by visakh16

quote:
Originally posted by russs

quote:
Originally posted by stepson

hello
try this

Thank you stepson, but is there a solution with standard sql ? (without the row() functions)

thank you

what do you mean by "standard sql"
All the posted solutions using standard functions of t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ansi sql, pure sql

russs
Starting Member

12 Posts

Posted - 2013-06-24 : 04:33:03

quote:
Originally posted by SwePeso

SELECT Code, Create_Data, MIN(Quantity) AS Qty FROM dbo.Table1 GROUP BY Code, Create_Date

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

thank you SwePeso :)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources