Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Max () of a Max() 2

Status
Not open for further replies.
inarobis

inarobis

IS-IT--Management
Joined
Apr 3, 2006
Messages
71
Location
CH
Good morning,

following a select statement with a max() in the valuedate and name = Marianne and Amounttype = USD


valuedate |name |Exec_date |Amout
-----------------------------------------------------
2006-02-28 |Marianne |2006-03-27 14:41:07.167 |200
2006-02-28 |Marianne |2006-03-28 14:01:28.123 |121
2006-02-28 |Marianne |2006-03-29 10:05:32.843 |224

Now I would like to obtain the max of exec_date and have just one record.

valuedate |name |Exec_date |Amout
-----------------------------------------------------
2006-02-28 |Marianne |2006-03-29 10:05:32.843 |224


Do you think that I need to create a view with the first query and max the exec_date.

inarobis
 
Sort by date Sort by votes
Nope. Wrap existing query into derived table:

Code: 
SELECT TOP 1 * 
FROM 
( <query here without ORDER BY clause>
) blah
ORDER BY Amount desc

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
like this


SELECT TOP 1 * FROM
(myquery) ORDER BY Amount desc

I have this mistake
incorrect syntax near ')'.


 
Upvote 0 Downvote
No 'blah'? [smile]

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
here is one way without a view but i am sure there are many others.. the view however makes the query look simpler. My solution does not involve a view its just an order by with a top statement..


create table yourtable(
valuedate datetime,
[name] varchar(50),
Exec_date datetime,
amount money,
Amounttype char(5)
)

insert yourtable(valuedate,[name],Exec_date,amount,Amounttype)
values ('2006-02-28','Marianne','2006-03-27 14:41:07.167',200,'USD')

insert yourtable(valuedate,[name],Exec_date,amount,Amounttype)
values ('2006-02-28','Marianne','2006-03-28 14:01:28.123',121,'USD')

insert yourtable(valuedate,[name],Exec_date,amount,Amounttype)
values('2006-02-28','Marianne','2006-03-29 10:05:32.843',224,'USD')

insert yourtable(valuedate,[name],Exec_date,amount,Amounttype)
values('2006-03-28','Julian','2006-04-03 10:05:32.843',224,'USD')

select top 1 * from yourtable
where valuedate in (select max(valuedate) from yourtable
group by [name],Amounttype
having name='Marianne' and Amounttype='USD' )

order by Exec_date DESC
 
Upvote 0 Downvote
Thanks a lot :) :) :)
What means blah?

inarobis
 
Upvote 0 Downvote
Derived table name. Since tables of any kind cannot be nameless, it is required - even if not explicitely used in code.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Thank you guys, really helpful I have a question can I do a view of view or several subqquery for example I would like to liste all people and they last amount.

Inarobis
 
Upvote 0 Downvote
Post table structures + sample data here...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
HEre there is a little sample of what I would like to do:

USER(
ID_USER char (10),PRIMARY KEY
NAME varchar (50))


ORDER (
ID_ORDER char (10) PRIMARY KEY
ID_USER char (10)
AMOUNT char (10)
VALUEDATE date
EXEC_DATE datetime
AMOUNTTYPE char (10))

insert into user (id_user, name) values (152,'Marianne')
insert into user (id_user, name) values (143,'Julina')
insert into user (id_user, name) values (145,'Karl')
insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '152', '2006-02-28','2006-02-27 14:41:07.167',200,'USD')

insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '152', '2006-02-28', '2006-02-27 15:41:07.167',400,'USD')

insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '152', '2006-02-28', '2006-03-31 16:41:07.167',300,'USD')

iinsert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '143', '2006-03-30', '2006-04-15 16:41:07.167',300,'USD')

insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '143', '2006-03-30, '2006-03-30 17:35:07.167',300,'USD')

insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '145', '2006-03-07, '2006-03-15 18:42:07.167',250,'USD')

insert into order(id_order, id_user, valuedate, amount, valuedate, Exec_date,Amounttype)
values (1, '145', '2006-03-07, '2006-03-30 17:42:07.167',300,'USD')


I did:

SELECT n.valuedate, name, o_exec_date, o.amount
FROM ORDER 0 join USER u on o.id_user=u.id_user,
(SELECT max (valuedate) as maxdate, exec_date
FROM ORDER
GROUP BY exec_date ) maxresults
WHERE n.exec_date = maxresults.exec_date
and n.valuedate= maxresults.maxdate and amounttype = 'USD' order by value


I had that:
valuedate |name |Exec_date |Amout
-----------------------------------------------------
2006-02-28 |Marianne |2006-02-27 14:41:07.167|200
2006-02-28 |Marianne |2006-02-27 15:41:07.167|400
2006-02-28 |Marianne |2006-03-31 16:41:07.167|300

With you help I have now


SELECT TOP 1 valuedate, name, exec_date, amount From (SELECT n.valuedate, name, o_exec_date, o.amount
FROM ORDER 0 join USER u on o.id_user=u.id_user,
(SELECT max (valuedate) as maxdate, exec_date
FROM ORDER
GROUP BY exec_date ) maxresults
WHERE n.exec_date = maxresults.exec_date
and n.valuedate= maxresults.maxdate) blah) order by value

valuedate |name |Exec_date |Amout
-----------------------------------------------------
2006-02-28 |Marianne |2006-03-31 16:41:07.167|300

Now I would like to have:

Select name, amount from (
SELECT TOP 1 valuedate, name, exec_date, amount From (SELECT n.valuedate, name, o_exec_date, o.amount
FROM ORDER 0 join USER u on o.id_user=u.id_user,
(SELECT max (valuedate) as maxdate, exec_date
FROM ORDER
GROUP BY exec_date ) maxresults
WHERE n.exec_date = maxresults.exec_date
and n.valuedate= maxresults.maxdate) blah) x join USER us x.user_id = us.user_id) blah order by value

I would like to have this
valuedate |name |Exec_date |Amout
-----------------------------------------------------
2006-02-28 |Marianne |2006-03-31 16:41:07.167|300
2006-02-30 |Julian |2006-03-31 18:42:07.167|250
2006-03-15 |Karl |2006-03-30 17:42:07.167|300

Inarobis
 
Upvote 0 Downvote
Ugh... it took me 10 minutes to clean up this data :X

Are you sure about results for Julian?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Anyway... I guess you need last row based on Exec_date for each id_user. In that case TOP 1 is not good. Try something like:
Code: 
select O.valuedate, U.name, O.exec_date, O.amount
from [Order] O
inner join [User] U on O.id_user = U.id_user
inner join
(	select ID_USER, max(EXEC_DATE) as max_exec_date
	from [Order]
	group by ID_USER
) OMAX
on O.id_user = OMAX.id_user and O.exec_date = OMAX.max_exec_date
order by U.id_user

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
I am really sorry for that, very kind from you to help me thank a lot :D :) :D

Inarobis
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
751
grnzbra
grnzbra
peac3
  • Locked
  • Question Question
fill the gap of hierarchy data
Replies
11
Views
1K
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question Question
How Do I Add Column to Middle of Table
Replies
4
Views
1K
BobSuruncle
BobSuruncle
SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
889
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top