SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help on a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

russs
Starting Member

12 Posts

Posted - 06/22/2013 :  11:14:56  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
417 Posts

Posted - 06/23/2013 :  01:02:05  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
417 Posts

Posted - 06/23/2013 :  01:03:36  Show Profile  Reply with Quote

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



Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

russs
Starting Member

12 Posts

Posted - 06/23/2013 :  09:32:47  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 06/23/2013 :  14:01:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/24/2013 :  00:56:22  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 06/24/2013 :  02:30:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe OP is using Access?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

russs
Starting Member

12 Posts

Posted - 06/24/2013 :  04:30:29  Show Profile  Reply with Quote
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
Go to Top of Page

russs
Starting Member

12 Posts

Posted - 06/24/2013 :  04:33:03  Show Profile  Reply with Quote
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 :)))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000