help with 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-25 : 03:27:40

Hi,

I have the following table I am running a SELECT statement on, but I am not getting my desired results.

I am trying to do SELECT statement that brings back all the UserID's only once, where the userID's have a rows in the table. I also want to display how many rows they have total, and order these userID's by the userID's with the Max(photoAdded). I'm not sure what I am doing wrong.

If anybody can lend a hand, its very much appreciated !!

Thanks once again,
mike123

I am getting results as such by running the query below.
(You can see I am having the same userID brought back more than once)

701408 1 2007-04-25 00:06:15.650
701408 1 2007-04-25 00:03:35.303
211667 1 2007-04-24 23:50:43.383
701408 1 2007-04-24 23:50:27.553
226650 5 2007-04-24 23:38:26.053
226650 1 2007-04-24 23:37:27.007
768424 3 2007-04-24 23:33:09.460

SELECT DISTINCT(FP.userID), count(*) totalPics, dateAdded, FROM tblfavouritephotos FP

GROUP BY FP.userID, dateAdded
ORDER BY dateAdded DESC

CREATE TABLE [dbo].[tblFavouritePhotos](
[favID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NOT NULL,
[counterID] [int] NOT NULL,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 03:34:27

[code]
SELECT FP.userID, count(*) AS totalPics, max(dateAdded) AS m_dateadded
FROM tblfavouritephotos FP
GROUP BY FP.userID
ORDER BY m_dateadded desc
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources