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

Development Tools

Reporting Services Development

query

Author

Topic

Frki
Starting Member

4 Posts

Posted - 2005-05-19 : 18:48:08

Hi all, i will try to explain a problem that i have..

i have these fields in my table :
- userid
- date
- time
- post

most users have more than one post. i need to get the last post (order by date and time) for each users, without repeating users.

For example, if the table contains these data :

userid date time post
----------------------------------
1 1.1.05 10:00 p1
2 10.1.05 10:00 p2
1 10.1.05 10:20 p3
3 12.1.05 10:03 p4
1 15.1.05 10:50 p5
3 14.1.05 10:06 p6
----------------------------------

the result i need should be :

userid date time post
----------------------------------
1 15.1.05 10:50 p5
2 10.1.05 10:00 p2
3 14.1.05 10:06 p6
----------------------------------

is there any way to get this result with one select query?

i hope you understand me, english is not my native langugae, sorry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 18:53:52

You should have date and time in one column. Here is the solution for that:



SET NOCOUNT ON

DECLARE @Table1 table (userid int, dt datetime, post char(2))

INSERT INTO @Table1 VALUES(1, '1/1/2005 10:00', 'p1')
INSERT INTO @Table1 VALUES(2, '1/10/2005 10:00', 'p2')
INSERT INTO @Table1 VALUES(1, '1/10/2005 10:20', 'p3')
INSERT INTO @Table1 VALUES(3, '1/12/2005 10:03', 'p4')
INSERT INTO @Table1 VALUES(1, '1/15/2005 10:50', 'p5')
INSERT INTO @Table1 VALUES(3, '1/14/2005 10:06', 'p6')

SELECT t1.userid, t1.dt, t1.post
FROM @Table1 t1
INNER JOIN
(
	SELECT userid, MAX(dt) AS dt
	FROM @Table1
	GROUP BY userid
) t2
ON t1.userid = t2.userid AND t1.dt = t2.dt
ORDER BY t1.userid

Tara

Frki
Starting Member

4 Posts

Posted - 2005-05-19 : 19:23:28

hi tara

well, i have no much experience in sql, and im not sure i completely understand this code, could you give me some explanation about it, please?

and thank you very much for the solution :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 19:25:05

Only look at the select, the rest is just setting up a test of your table and data for me to test on my machine.

The derived table, query on the inside, gets you the userid and date and time of the post. You can't get the post column though so you have to join to this derived table to get that information. You join on all columns involved in the derived table.

Tara

Frki
Starting Member

4 Posts

Posted - 2005-05-19 : 19:59:37

one more question, what should i do if there are two separate cols for date and time?! Can i use MAX function (i think i cant) than?

another problem is that i cant create derivated table to do any type of JOIN (table t2)

i could have only one recordset :(

i know there are too many restrictions, and, unfortunately, i dont deside about it :((((

in any way, thanx a lot for help, tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 20:04:34

You can concatenate your date and time columns so that they are one column in the query.

Here you go:



SET NOCOUNT ON

DECLARE @Table1 table (userid int, dt varchar(100), tm varchar(100), post char(2))

INSERT INTO @Table1 VALUES(1, '1/1/2005', '10:00', 'p1')
INSERT INTO @Table1 VALUES(2, '1/10/2005', '10:00', 'p2')
INSERT INTO @Table1 VALUES(1, '1/10/2005', '10:20', 'p3')
INSERT INTO @Table1 VALUES(3, '1/12/2005', '10:03', 'p4')
INSERT INTO @Table1 VALUES(1, '1/15/2005', '10:50', 'p5')
INSERT INTO @Table1 VALUES(3, '1/14/2005', '10:06', 'p6')

SELECT t1.userid, t1.dt, t1.tm, t1.post
FROM @Table1 t1
INNER JOIN
(
	SELECT userid, MAX(dt + ' ' + tm) AS dtm
	FROM @Table1
	GROUP BY userid
) t2
ON t1.userid = t2.userid AND t1.dt + ' ' + t1.tm = t2.dtm
ORDER BY t1.userid

quote:

another problem is that i cant create derivated table to do any type of JOIN (table t2)

i could have only one recordset :(

Why can't you use a derived table? Who is giving you this requirement?

You only get one recordset from my query. Have you even tried running it inside Query Analyzer to check it out?

If you don't care about the post column in the recordset, this solution is much simpler:

SELECT userid, MAX(dt + ' ' + tm) AS dtm
FROM @Table1
GROUP BY userid

Tara

Frki
Starting Member

4 Posts

Posted - 2005-05-19 : 20:53:45

thats all because i have used a specific kind of development tool (some kind of half-programming language). i had to use that one, please dont ask me why :)

you are right, i have never even tried to run your code..im sorry, as i said i cant use your code as you posted to me, i have to translate it to exceptable form of code before i use it (here is where i got more than one recordset).

on the other side, i only got table's structure... i didnt create it, even i have never added or updated any record in it, and now i have to make a specific report from that table

as u can see i got a big problem, and ... never mind, its, at all, only my problem ;)

only i can tell is that you are a great, great man
thank you, thank you ... :)))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-20 : 12:36:52

First, I'm a female.

Second, is this for school? If it isn't, then just use Query Analyzer to write your T-SQL code. Don't use some other tool. And if someone is requiring this, ask them why.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources