how to fetch Name,Sex from UserInfo table (male and female show alternatively)

Question

1.67/5 (2 votes)

See more:

SQL-Server-2008R2

UserINfo Table

what is the select query to show like the below table

SQL

+-----+--------+-------+
|   1 | female | Lisa  |
|   2 | male   | Greg  |
|   3 | female | Mary  |
|   4 | male   | John  |
|   5 | female | Jenny |
+-----+--------+-------


Sql Query:: select Id,Name,Sex from UserINfo order by ROW_NUMBER() over (PARTITION BY Sex order by Id),Sex asc

I want use distinct for Name..What would be the query? 
and if i use inner join what would be the query

Posted 28-Oct-14 2:28am

aiswarjya

Updated 28-Oct-14 21:13pm

v4

Add a Solution

Comments

Sanchayeeta 28-Oct-14 8:54am

Show your table structure.

King Fisher 28-Oct-14 8:57am

show your table with Sample Data's .

Kornfeld Eliyahu Peter 28-Oct-14 8:58am

http://mattgemmell.com/what-have-you-tried/

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh waran · Accepted Answer · 2014-10-28T03:16:00

Solution 2

Try to add your table id here,

SQL

select Id,Name,Sex from UserINfo order by ROW_NUMBER() over (PARTITION BY Sex order by Id),Sex asc

(Or) try Group by

SQL

select Id,Name,Sex from UserINfo group by Name,Id,Sex order by ROW_NUMBER() over (PARTITION BY Sex order by Id),Sex asc

Posted 28-Oct-14 3:16am

Rajesh waran

Updated 28-Oct-14 21:26pm

v2

Comments

vangapally Naveen Kumar 28-Oct-14 9:28am

nice answer....

Rajesh waran 28-Oct-14 9:30am

Thank You.

King Fisher 29-Oct-14 0:21am

Good Job Gopu Raj my 5+

Rajesh waran 29-Oct-14 0:27am

Thank you @King_Fisher

aiswarjya 29-Oct-14 2:48am

in this query i want to use "DISTINCT".Please Help me

King Fisher 29-Oct-14 3:19am

Refer my Solution :)

Rajesh waran 29-Oct-14 3:27am

Rather than use SELECT DISTINCT, use GROUP BY to get the sort order.
I have updated my answer, please check out

King Fisher · Accepted Answer · 2014-10-28T21:19:00

Solution 3

Try this : with Distinct function

SQL

select  distinct id,Name,Sex , ROW_NUMBER() over (PARTITION BY Sex order by id)as rows from UserINfo  order by rows,sex asc

Posted 28-Oct-14 21:19pm

King Fisher

Comments

tewodrosabebe 21-Apr-18 13:44pm

write the program vb.net which displays you first name last name,your sex and your id declare first and last name global s
of string type