how to create sub-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

General SQL Server Forums

New to SQL Server Programming

how to create sub-query

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-15 : 06:05:30

i have table like this


Product	Date	values of sales
A	6-Jan-09	4
A	13-Jan-09	345
A	14-Jan-09	5
A	24-Jan-09	-9
B	1-Jan-09	51
B	5-Jan-09	4
B	7-Jan-09	4
B	8-Jan-09	7
B	9-Jan-09	5
B	23-Jan-09	4
B	25-Jan-09	4
B	26-Jan-09	4
B	27-Jan-09	4
B	28-Jan-09	4
C	15-Jan-09	4
C	16-Jan-09	4
D	21-Jan-09	4
D	22-Jan-09	4

I need to write a query to list each product and value which has added recently

for example for product A, The result will be


A	6-Jan-09	4

please help me..
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 06:47:33

What data type is Date column? DATETIME or VARCHAR?

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

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-15 : 06:49:43

is a DATETIME

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 06:53:01

select product,date,valuesofsales from urtable where date in ( select min(date) from urtable group by product) and product = 'A'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 06:55:30

[code]SELECT Product,
Date,
ValueOfSales
FROM (
SELECT Product,
Date,
ValueOfSales,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Date DESC) AS recID
FROM Table1
) AS d
WHERE recID = 1[/code]

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

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-15 : 06:58:18

Hi,
Please check the given query that bring your result.

declare @Table1 table(Product char(1), Date DateTime, Sales decimal(18,2))
insert into @Table1 values('A','6-Jan-09',4)
insert into @Table1 values('A', '13-Jan-09',345)
insert into @Table1 values('A', '14-Jan-09',5)
insert into @Table1 values('A', '24-Jan-09',-9)
insert into @Table1 values('B', '1-Jan-09', 51)
insert into @Table1 values('B', '5-Jan-09', 4)
insert into @Table1 values('B', '7-Jan-09', 4)

select * from @Table1 a
inner join
(select Product, Max(Date) Date from @Table1
group by Product) b on a.Product=b.Product and a.Date=b.Date

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-15 : 07:06:44

Hi Peso,
your solution worked perfectly..

thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-15 : 08:57:55

Also see what you can do with Row)number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-04-17 : 03:20:10

ROW_NUMBER is not available in sql server v8.0, can you please suggest some alternatives

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 03:36:25

Row_number() function is not available in SQL server 2000.

what is the alternative for this

see this topic
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123849[/url]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:39:46

quote:
Originally posted by krishna_yess

Row_number() function is not available in SQL server 2000.

what is the alternative for this

see this topic
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123849[/url]

It has circular reference now

Madhivanan

Failing to plan is Planning to fail

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 03:44:02

any alternatives?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 04:12:52

quote:
Originally posted by krishna_yess

any alternatives?

declare @Table1 table(Product char(1), Date DateTime, Sales decimal(18,2))
insert into @Table1 values('A','6-Jan-09',4)
insert into @Table1 values('A', '13-Jan-09',345)
insert into @Table1 values('A', '14-Jan-09',5)
insert into @Table1 values('A', '24-Jan-09',-9)
insert into @Table1 values('B', '1-Jan-09', 51)
insert into @Table1 values('B', '5-Jan-09', 4)
insert into @Table1 values('B', '7-Jan-09', 4)

select a.* from @Table1 a
inner join
(select Product, min(Date) Date from @Table1
group by Product) b on a.Product=b.Product and a.Date=b.Date

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 04:35:04

did u try the solution given by me on 04/15/2009 : 06:53:01

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 05:35:40

yes bklr, i tried that. it results duplicate product..
i think this is because there are 2 date entries with same value


select product,date,valuesofsales from urtable where date in ( select 
min(date) from urtable group by product)

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 05:53:41

[code]
select product,date,valuesofsales from urtable where date in ( select
min(date) from urtable group by product)
[/code]

if i use this, the valueOfSales field is getting summed

?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-02 : 08:50:09

Hi

SELECT
ROWID=IDENTITY(INT,1,1),
NAME,
ID,
XTYPE
INTO #tempsysobjects
FROM SYSOBJECTS

NOTE
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:18:30

quote:
Originally posted by krishna_yess

select product,date,valuesofsales from urtable where date in ( select
min(date) from urtable group by product)

if i use this, the valueOfSales field is getting summed

?

thats wrong...you're either using it incorrectly or your full query contains something more in that case

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources