Retrieving rows from mySql Table using Groupby

Question

I have a table

Table

I am using the following query

select max(dblVersion),sName,fCompleted,ixLastModifiedBy,dtCreatedAt,dtLastModifiedAt,fStatus from tblechecklisttemplateversion group by ixTemplate

I am getting the below table as output Table2

The row values of other columns are not corresponding to the dblVersion Column .. How to display the corresponding row values

In Table2 the dtCreated should be 2012-04-05 02:57:36 and fstatus Should be 1 and dbLastModifiedat Should be 2012-04-05 02:57:36 .. Thus the corresponding column values are not displayed except for fCompleted=0 .. Compare the Table1 and Table2 — Manoj Nayak, May 04 '12 at 11:14

bitoshi.n · Accepted Answer · 2012-05-04T11:30:06.790

1

Try this

SELECT 
   a.dblVersion,
   a.sName,
   a.fCompleted,
   a.ixLastModifiedBy,
   a.dtCreatedAt,
   a.dtLastModifiedAt,
   a.fStatus 
FROM 
   tblechecklisttemplateversion a
JOIN (
    SELECT 
        ixTemplate,
        max(dblVersion) as dblVersion 
    FROM 
        tblechecklisttemplateversion 
    GROUP BY 
        ixTemplate) as b
ON 
    a.ixTemplate=b.ixTemplate and a.dblVersion=b.dblVersion

edited May 04 '12 at 11:30

answered May 04 '12 at 11:10

bitoshi.n

2,278
1
16
16

it is giving an Error .. Column 'dblVersion'in field list is ambiguous. – Manoj Nayak May 04 '12 at 11:22
1

Upss... sorry. it's need `a` to avoid ambiguous – bitoshi.n May 04 '12 at 11:30

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

This might help you..

MySQL - Get row number on select

changed query -

  SELECT 0 INTO @x;
  select (@x:=@x+1) as  
  rownumber,max(dblVersion),sName,fCompleted,ixLastModifiedBy,dtCreatedAt,
  dtLastModifiedAt,fStatus 
  from tblechecklisttemplateversion group by ixTemplate

edited May 23 '17 at 12:30

Community

1
1

answered May 04 '12 at 11:05

Sanath

4,774
10
51
81

its Giving the same result as of earlier – Manoj Nayak May 04 '12 at 11:27

Retrieving rows from mySql Table using Groupby

2 Answers2