sql grouping categories

Question

2.60/5 (3 votes)

See more:

I am trying to group items within a category. For example, I have a list of objects with the an id and date that have a subcategory with multiple ids and dates. Within that subcategory, i have to select the id and date with the max date. All four of these fields are in the same table. I have used rank, subselects, joins, etc. And what i get is either all the records or a top 1 of the whole table. I am at a loss. Any suggestions??

What is in the table is category id, date, subcategoryid, date. What I need to do is select categoryid, date, subcategoryid, (max)date from table.
what I have done is this:

SQL

select categoryid
,categorydate
,subcatid
,subcategorydatetime
from table t
inner join ( select categoryid, max(subcategorydate) from table group by categoryid) t1
on t.categoryid= t1.categoryid
and t.subcategorydatetime=t1.subcategorydatetime

example data would be

categoryid      date        subcategoryid   date
        123     5/01/2013   12345           04/12/2013
        123     5/01/2013   23456           03/21/2013
        123     5/01/2013   32165           02/02/2012

The results that I would want would be

categoryid  date         subcategoryid    date
123         05/01/2013   12345            04/12/2013

Posted 20-May-13 5:53am

ommie1976

Updated 21-May-13 7:00am

Kenneth Haugland

v8

Add a Solution

Comments

[no name] 20-May-13 11:56am

"Any suggestions", yes. Show us what your input is, show is what your expected output is, show us what you are tried to do, describe what the problem is.

gvprabu 20-May-13 11:59am

give some more details about your issue.

Kschuler 20-May-13 16:33pm

Could you provide a simple sample of what your data would be and what you want to select from that sample data?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-20T08:00:00

You did not provide enough information to give you exact suggestion. There is only one: you need to read about aggregate functions:

http://www.w3schools.com/sql/sql_groupby.asp[^]
http://msdn.microsoft.com/en-us/library/ms177673%28v=sql.100%29.aspx[^]
http://www.w3schools.com/sql/sql_func_max.asp[^]
http://msdn.microsoft.com/en-us/library/aa933240%28v=sql.80%29.aspx[^]

[EDIT #3]

SQL

DECLARE @tbl TABLE (categoryid INT, catdate DATETIME,  subcategoryid INT,  subcatdate DATETIME)

INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(123, '5/01/2013', 12345, '04/12/2013')
INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(123, '5/01/2013', 23456, '03/21/2013')
INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(123, '5/01/2013', 32165, '02/02/2012')
INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(124, '5/01/2013', 36598, '02/02/2012')
INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(124, '5/01/2013', 52487, '02/02/2012')
INSERT INTO @tbl (categoryid, catdate, subcategoryid, subcatdate)
VALUES(124, '5/01/2013', 78954, '04/02/2012')


SELECT t1.categoryid, t1.catdate, t1.subcategoryid, t2.subcatdate
FROM @tbl AS t1 RIGHT JOIN (
    SELECT categoryid, MAX(subcatdate) AS subcatdate
    FROM @tbl
    GROUP BY categoryid
    ) AS t2 ON t1.categoryid = t2.categoryid AND t1.subcatdate = t2.subcatdate

Results:

123	2013-05-01 00:00:00.000	12345	2013-04-12 00:00:00.000
124	2013-05-01 00:00:00.000	78954	2012-04-02 00:00:00.000

[/EDIT]