I need select sql query

Raja A

7y1k

For example table student ... Fields stuid,name,groupid,joindate... From select query select name max count of groupid from the table... If two max count groupid is equal take the maximum join date name? I need single SQL query... can anyone help me?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
try this query:
select max(column_name) from tbname where column_name<(select max(column_name) from table_name )
0
Shahriar Nikkhah7y ago
Hi I think the best approch is Ranking Within CTE

In the CTE you will need two steps (or you may be able to have them in one step)
step CTEPart1 : Rank max count groupid
Step CTEPart2 : maximum join date name
Step CTEPart3 : Join Part1 and Part2

good luck
0
Sanwar Ranwa7y ago
Hello Raja

Please check thios query its working
create table tr (stuid int ,name varchar(50),groupid int,joindate date)


insert into tr values(1,'A',1,'1-2-2018')
insert into tr values(2,'Ac',2,'1-2-2019')
insert into tr values(3,'Ac',2,'1-6-2018')
insert into tr values(4,'Ad',4,'1-2-2018')
insert into tr values(3,'Ac',4,'1-6-2018')


--check this query

select groupid,Max(joindate)joindate from tr group by groupid having count(*)  =
(select max(cnt) from (select count(*) cnt from tr group by groupid)a)

drop table tr
+1
Saravanan Np7y ago
Hi Raja,

Better you try this below query.

SELECT stuid,name,groupid,joindate,MAX(name ) as name FROM `student`
0