Reg select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reg select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-15 : 01:18:38

Hi experts,

I have two tables tbl_reqforproposal,tbl_country

tbl_reqforproposal

RFP_Requestid ,RFP_Country ,RFP_Name
1 98 A
2 3 B
3 98 c
4 98 D
5 1 E
6 1 F
7 3 G
8 3 H
9 98 I

tbl_country

Country ,Country_name
1 United States
2 PAK
3 UK
4 United Arab Emirates
5 Austria
98 INDIA

please find the expected output below,

based on the availability of country i need to display the result, for example in tbl_reqforproposal, the ref_country india occurs four time so i want to display india first, then uk three times i want to display uk secound and then United States two times display i want to display last

Please help this concern

REF_Name ,Country_name
A INDIA
C INDIA
D INDIA
I INDIA
B UK
G UK
H UK
E United States
F United States

Thanks an Advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 01:24:44

select rfp_name,country_name from tbl_reqforproposal r
inner join tbl_country c on r.RFP_Country = c.Country
order by country_name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 01:34:19

I assumed you are using 2005/2008


DECLARE @tbl_reqforproposal TABLE
(
	RFP_Requestid	int,
	RFP_Country	int,
	RFP_Name	CHAR
)
INSERT INTO @tbl_reqforproposal
SELECT	1, 98, 'A'	UNION ALL
SELECT	2,  3, 'B'	UNION ALL
SELECT	3, 98, 'C'	UNION ALL
SELECT	4, 98, 'D'	UNION ALL
SELECT	5,  1, 'E'	UNION ALL
SELECT	6,  1, 'F'	UNION ALL
SELECT	7,  3, 'G'	UNION ALL
SELECT	8,  3, 'H'	UNION ALL
SELECT	9, 98, 'I'

DECLARE @tbl_country TABLE
(
	Country		int,
	Country_name	varchar(20)
)
INSERT INTO @tbl_country
SELECT	 1, 'United States'		UNION ALL
SELECT	 2, 'PAK'			UNION ALL
SELECT	 3, 'UK'			UNION ALL
SELECT	 4, 'United Arab Emirates'	UNION ALL
SELECT	 5, 'Austria'			UNION ALL
SELECT	98, 'INDIA'

SELECT	r.RFP_Name, c.Country_name
FROM	@tbl_reqforproposal r
	INNER JOIN @tbl_country c	ON	r.RFP_Country	= c.Country
ORDER BY COUNT(*) OVER (PARTITION BY c.Country_name) DESC, r.RFP_Name

KH
[spoiler]Time is always against us[/spoiler]

RajuY
Starting Member

1 Post

Posted - 2009-07-15 : 02:53:34

quote:

Originally posted by khtan

I assumed you are using 2005/2008


DECLARE @tbl_reqforproposal TABLE
(
	RFP_Requestid	int,
	RFP_Country	int,
	RFP_Name	CHAR
)
INSERT INTO @tbl_reqforproposal
SELECT	1, 98, 'A'	UNION ALL
SELECT	2,  3, 'B'	UNION ALL
SELECT	3, 98, 'C'	UNION ALL
SELECT	4, 98, 'D'	UNION ALL
SELECT	5,  1, 'E'	UNION ALL
SELECT	6,  1, 'F'	UNION ALL
SELECT	7,  3, 'G'	UNION ALL
SELECT	8,  3, 'H'	UNION ALL
SELECT	9, 98, 'I'

DECLARE @tbl_country TABLE
(
	Country		int,
	Country_name	varchar(20)
)
INSERT INTO @tbl_country
SELECT	 1, 'United States'		UNION ALL
SELECT	 2, 'PAK'			UNION ALL
SELECT	 3, 'UK'			UNION ALL
SELECT	 4, 'United Arab Emirates'	UNION ALL
SELECT	 5, 'Austria'			UNION ALL
SELECT	98, 'INDIA'

SELECT	r.RFP_Name, c.Country_name
FROM	@tbl_reqforproposal r
	INNER JOIN @tbl_country c	ON	r.RFP_Country	= c.Country
ORDER BY COUNT(*) OVER (PARTITION BY c.Country_name) DESC, r.RFP_Name

KH
[spoiler]Time is always against us[/spoiler]

Or
SELECT r.RFP_Name, c.Country_name
FROM @tbl_reqforproposal r
INNER JOIN @tbl_country c ON r.RFP_Country = c.Country
GROUP BY c.Country_name ,RFP_Name
ORDER BY c.Country_name,RFP_Name

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-15 : 03:34:11

Thanks for your reply. Actually what I need is,

I have two tables tbl_reqforproposal,tbl_country

I want to list all the countries. It should display in the order, the more time inserted country (RFP_Country) in tbl_reqforproposal should come at top.

tbl_reqforproposal
RFP_Requestid ,RFP_Country ,RFP_Name
1 98 A
2 3 B
3 98 c
4 98 D
5 1 E
6 1 F
7 3 G
8 3 H
9 98 I

tbl_country
Country ,Country_name
1 United States
2 PAK
3 UK
4 United Arab Emirates
5 Austria
98 INDIA

please find the expected output below,

based on the availability of country i need to display the result, for example in tbl_reqforproposal, the ref_country india occurs four time so i want to display india at first position, then uk three times it should display uk at secound position and then United States two times then it should display US at third position and other countries sholud follow up like this.

Please us help in this concern

Country_name
INDIA
UK
United States
United Arab Emirates
Austria
PAK

Thanks in Advance

quote:
Originally posted by bklr

select rfp_name,country_name from tbl_reqforproposal r
inner join tbl_country c on r.RFP_Country = c.Country
order by country_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources