SQL Question - Putting specific record first

[TechBoyJK]

Hi Guys,

I'm running a query on a 'contact info' table that is basically a list of addresses for an account. The primary key for this table is 'address_id'

A previous query has already determined which of the 'address_id' records is they 'primary' address.

I want to query for all of the addresses for a specific member, but have the 'primary' address be the first in the list.

I was thinking about doing a union on two selects.. first select grabbing only record that matches the primary id, and the 2nd grabbing all the records that don't match.. then I'd have the first record being the primary, and then all the rest following..

Ideas?

	SELECT *
	
	FROM tbl_member_contacts
		
	WHERE member_id = #member_id#
	and contact_id = #primary_contact_id#

	UNION

	SELECT *
	
	FROM tbl_member_contacts
		
	WHERE member_id = "#member_id#"
	and contact_id <> "#primary_contact_id#"

 

[wanderer27]

I'm not sure I'm following you on this.

Maybe a small sample input and output?


As a vague thought, how about a Ranking function?

 

[brandonbull]

I think the db will try to return the data in order based on the selected columns. I guess if the primary_contact_id would be the highest or lowest you could sort by that column or add a derived column with a specific value for primary contact and the other contacts get a different number.


SELECT member_id,contact_id, 1 as contact_flag

FROM tbl_member_contacts

WHERE member_id = #member_id#
and contact_id = #primary_contact_id#

UNION

SELECT member_id,contact_id, 2 as contact_flag

FROM tbl_member_contacts

WHERE member_id = "#member_id#"
and contact_id <> "#primary_contact_id#"
order by contact_flag

 

[TechBoyJK]

I think the db will try to return the data in order based on the selected columns. I guess if the primary_contact_id would be the highest or lowest you could sort by that column or add a derived column with a specific value for primary contact and the other contacts get a different number.


SELECT member_id,contact_id, 1 as contact_flag

FROM tbl_member_contacts

WHERE member_id = #member_id#
and contact_id = #primary_contact_id#

UNION

SELECT member_id,contact_id, 2 as contact_flag

FROM tbl_member_contacts

WHERE member_id = "#member_id#"
and contact_id <> "#primary_contact_id#"
order by contact_flag

that worked! thanks.

 

[LokutusofBorg]

order by
	case when address_id = @firstAddressID then 0 else 1 end,
	some_other_field,
	some_other_other_field