Member Avatar for Pari13

Hello All Developeres,

Please Read Below Senario.
I have one table Supposed "Emp(EmpID,EmpName)"
Now There is data with Name "testName1" and "testName2" in EmpName Field

Now I need query which fetch all record in asceding order for EmpName Field
and Record with "testName1" and "testName2" will must display at last.

Please Wait for your reply.....
Thanks....
Pari13...

  • 4 Contributors
  • 3 Replies
  • 128 Views
  • 8 Months Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Short answer: you do not need UNION to put two specific names at the bottom — just add a custom sort key that marks those names as “late” and then sort by EmpName. 's plain alphabetical sort won't move the two names to the end, and 's UNION+GROUP BY is unnecessary and can be invalid under strict SQL modes. 's CASE idea is a correct pattern; here are a couple of alternative MySQL-friendly options that are simpler or more flexible.

A compact approach uses a boolean expression in ORDER BY so non-matching names sort first, then everything is alphabetized:

select EmpID, EmpName
from Emp
order by (EmpName in ('testName1','testName2')) asc, EmpName asc;

If you prefer to control the order of the special names explicitly (or keep them in the order you list), the FIELD() function is handy:

select EmpID, EmpName
from Emp
order by field(EmpName, 'testName1','testName2') asc, EmpName asc;

For better maintainability or performance on large tables, store priority values in a tiny lookup table and join to it. That lets you index the priority column and avoid recalculating expressions in ORDER BY:

-- small priority table
create table NamePriority (name varchar(255) primary key, pr tinyint);
insert into NamePriority (name, pr) values ('testName1',1), ('testName2',1);

select e.EmpID, e.EmpName
from Emp e
left join NamePriority p on e.EmpName = p.name
order by coalesce(p.pr, 0) asc, e.EmpName asc;

Notes and cautions: GROUP BY without aggregation is unsafe in modern MySQL when ONLY_FULL_GROUP_BY is enabled. Using functions in ORDER BY can stop index-only ordering; a small priority column and index can help. For MySQL docs see FIELD() and the ONLY_FULL_GROUP_BY mode for details.

Recommended Answers

All 3 Replies

Member Avatar for prit005

select EmpID,EmpName from Emp a group by a.EmpID,a.EmpName
union
select EmpID,EmpName from Emp a group by a.EmpID,a.EmpName order by EmpName

hi i think this query solve your problem

Edited by prit005 because: n/a
Member Avatar for debasisdas

try this

select EmpID,EmpName from Emp order by EmpName
Member Avatar for adam_k

Since you want 2 particular names to go last, I assume you know how to sort the results, but want the whole field sorted ascending and out of sort those 2 names in the bottom.
I'll give you an example for 2 names hardcoded and you can modify it with params or with another table or whatever.

select EmpID,EmpName from Emp 
order by case when EmpName in ('testName1','testName2') then 2 else 1 end,EmpName
Edited by adam_k because: n/a
commented: you got it +14
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.