Hi -

I have a table with a structure like

ID | TrackCode | DateTime

The "DateTime" column is a datetime column-type.

I'm trying to run a query that will return a list of all the Months and Years in which an entry was made for a specific "TrackCode".

So for example, if the table contains:

ID | TrackCode | DateTime
1 | A | 2002-10-15 23:29:35
2 | B | 2002-10-25 14:33:27
3 | B | 2002-11-08 17:05:57
4 | A | 2002-11-25 16:11:35
5 | A | 2002-12-01 19:36:01
6 | A | 2002-12-01 20:59:01

... and I want to filter against a TrackCode of "A", the query should return:

Year | Month
2002 | 12
2002 | 11
2002 | 10

Thus giving me a list of all the year/months in which an entry was made in the table.

I thought this would work:
SELECT DISTINCT YEAR(DateTime), MONTH(DateTime) FROM Table_Name WHERE TrackCode = A ORDER BY DateTime DESC

...And also tried this:
SELECT YEAR(DateTime), MONTH(DateTime) FROM Table_Name WHERE ID in (SELECT MAX(ID) from Shocker_Stats WHERE TrackCode = A GROUP BY MONTH(DateTime), YEAR(DateTime)) ORDER BY DateTime DESC

However, neither works. The first seems to return nothing, and the second gives a SQL syntax error.

Any ideas?
:)
Matt G

Hi Matt.

You might want to consider using the GROUP BY function for the Track Code column rather than using DISTINCT function.

Something like:


May be these codes will shed some light.
========================================
Buh Bye!

Cheers,
Me

Ah yes! GROUP BY... that's just what I needed!

I ended up with:

Code:

SELECT
YEAR(DateTime),
MONTH(DateTime)
FROM TableName
WHERE TrackCode = A
GROUP BY MONTH(DateTime), YEAR(DateTime)
ORDER BY DateTime DESC

I needed to GROUP BY the Month/Year rather than TrackCode, since I was only looking for the one TrackCode, not a list of all of them.

I really appreciate your help, Eliot. Thanks.

--
Matt G

No problem. Glad to help. Sorry for the confusion with regards to the TrackCode, I was unsure what you wanted to output/parse. But I am glad you figured it out.
========================================
Buh Bye!

Cheers,
Me