samedi 11 juillet 2015

Select Record with common column field

I have following table with duplicate id.I want all the record with duplicate id

CREATE TABLE Student1
    (`id` int,`status` int,`amount` int , `Name` varchar(10), `date` varchar(55))
;

INSERT INTO Student1
    (`id`,`status`,`amount`, `Name`, `date`)
VALUES
    (1,0,4500, 'ram', '04/02/2012'),
    (2,0,2000, 'shyam', '05/09/2013'),
    (2,0,6500, 'radhe', '11/11/2014'),
    (2,0,8000, 'radheshyam', '15/11/2013'),
    (4,0,1500, 'ghanshyam', '08/11/2014')
;

id  status  amount  Name         date
1   0       4500    ram         04/02/2012
2   0       2000    shyam       05/09/2013
2   0       6500    radhe       11/11/2014
2   0       8000    radheshyam  15/11/2013
4   0       1500    ghanshyam   08/11/2014

Query:

Select * from Student1 group by id having count(*)>1

Result:

id  status  amount  Name    date
2   0       2000    hyam    05/09/2013

Expected Result :

   id status    amount     Name        date
   2    0       2000      shyam      05/09/2013
   2    0       6500      radhe      11/11/2014
   2    0       8000    radheshyam   15/11/2013

Aucun commentaire:

Enregistrer un commentaire