Newbee question

  • Thread starter Thread starter rich
  • Start date Start date
R

rich

Hi,
Can anyone suggest a query to tackle the following problem?

MIXTABLE

ID MIXID DESCRIPTION LAST_MOD_BY
1 rec1 recipe1 rich
2 rec2 recipe2 matt
3 rec3 recipe3 rich
4 rec1 recipe1 matt


As a 'Mix' is modified the record isn't updated a new one is created so
that it is possible to view changes.

I would like to write a query that would give me a list of all the
mix's (no duplicates) and their version number. (version number
calculated by counting number of times mix id appears in recipe)
e,g.

ID MIXID VER DESCRIPTION LAST_MOD_BY
4 rec1 2 recipe1 matt
2 rec2 1 recipe2 matt
3 rec3 1 recipe3 rich


Is this easily done?

Thanks in advance

Rich
 
rich said:
Hi,
Can anyone suggest a query to tackle the following problem?

MIXTABLE

ID MIXID DESCRIPTION LAST_MOD_BY
1 rec1 recipe1 rich
2 rec2 recipe2 matt
3 rec3 recipe3 rich
4 rec1 recipe1 matt


As a 'Mix' is modified the record isn't updated a new one is created so
that it is possible to view changes.

I would like to write a query that would give me a list of all the
mix's (no duplicates) and their version number. (version number
calculated by counting number of times mix id appears in recipe)
e,g.

ID MIXID VER DESCRIPTION LAST_MOD_BY
4 rec1 2 recipe1 matt
2 rec2 1 recipe2 matt
3 rec3 1 recipe3 rich


Is this easily done?

Thanks in advance

Rich
Click to expand...


I forgot to say how far I got

SELECT MixTable.MixID, Count(MixTable.MixID)
FROM MixTable
GROUP BY MixTable.MixID

This doesn't display the other columns I need though, this is where I
was getting stuck.
 
Try this --
SELECT Last(rich.ID) AS LastOfID, rich.MIXID, Count(rich.DESCRIPTION) AS
VER, Last(rich.LAST_MOD_BY) AS [Last Mod By]
FROM rich
GROUP BY rich.MIXID;
 
Back
Top