Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

Romania
545 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
Aged Yak Warrior

Romania
545 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
30421 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
52326 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
30421 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  
 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