SQL server questions: order

Showing posts with label order. Show all posts

Thursday, March 22, 2012

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Each customer will only have one record for each Genre.

Table 2 Acc_holders

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

so far I have

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[Genre] VARCHAR(20) NOT NULL,
[Sales Person] VARCHAR(20),
[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL,
[Account No] CHAR(9) NOT NULL,
[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],
CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],
ac.Balance,
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],
CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]
FROM @.Sales
UNION
SELECT Cust_ID, [Name]
FROM @.Acc_holders) t
LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

Monday, March 19, 2012

A to Z table data

Hello

I am currently using ms sql 2000 and I want to display my table, column of names in alphabetical order. How can I achieve this?

Thanks

Laura

Did you mean to dispaly all column names of a table in alphabetical order? In T-SQL we use 'ORDER BY' clause in query to perform ordering. So let's use such a statement to achieve your request:

select * from syscolumns where id=object_id('myTable') order by name

|||

Thanks for the reply. Thanks also for the solution.

I was actually thinking about when I had previously created a database using Access. It had a nice easy to use feature that could be used on a column whilst building the database. When you click on the column within the database you can choose to display in assending or decending order. I was looking for such a feature in SQL but so far I have not found it. Does this feature exist? If so where is it?

Thanks

Laura

|||

Sure there is. In SQL we use 'ORDER BY' clause to sort result. You can also sort result returned in Enterprise Manager: Just rigth click a table->choose 'Open Table'->'Query', then in the 'Diagram Pane' add some columns, and you can choose 'Sort Type'&'Sort Order' for each column. Then click 'Run' to execute the query. You can press F1 in 'Diagram Pane' to get more help from SQL2000 Books Online.

Sunday, March 11, 2012

A Special Order By clause?

The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?

SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1
ORDER BY Country_Long

I think this will work with you:

 1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO

Good luck.

|||

You probably need to do 2 SELECTs and do a UNION. The first one will have 'United States' followed by the rest in alphabetical order.

|||

I read the question wrong Stick out tongue

What CS4Ever and ndinakar both have posted is how you want to do this.

|||

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

|||

SolitaryMan:
Thanks all!!
Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

Your are welcome SolitaryMan.

Thanks for posting another example. Actullay there is always more than a way to do the thing.

I believe (not sure) the example in my previous post in better in term of performance, because of the CASE in the ORDER BY clause for the example you menioned. Since database engine will check the case of each record (long processing) while this is not the case in my example.

Again, thanks for sharing the example.

|||

CS4Ever:

I think this will work with you:

 1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO

Good luck.

Change the order by clause to:

1ORDER BY 1, Country_Long

Good luck.

Tuesday, March 6, 2012

A Referense All Query

I have a table that contains a bunch of ID's and I am trying to write a query that will find every combination of id (order of t he ID's is not needed...IE: a,b is the same as b,a) but I cant seem to figure it out. Any help?

Here is my example:

Table

[ ID ]
[ a ]
[ b ]
[ c ]

Results

[ ID1 | ID2 ]
[ a | a ]
[ a | b ]
[ a | c ]
[ b | b ]
[ b | c ]

maybe this:

Code Snippet

create table #t (ID char(1))

insert into #t

select 'a'

union all select 'b'

union all select 'c'

;with cte as

(

select t1.id as id1, t2.id as id2, t1.cksum + t2.cksum as hash,

row_number() over(partition by t1.cksum + t2.cksum order by t1.id, t2.id) as rno

from

( select id, checksum(id) as cksum from #t) t1

full join ( select id, checksum(id) as cksum from #t) t2

on 1=1

)

select id1, id2

from cte

where rno = 1

Thursday, February 9, 2012

A good Database Modelling Tool for Sql Server 2005

Hello i am looking for a good database modelling tool in order to design an
sql server database.
I have recently come across with DBDesigner 4. Can you suggest me a better
alternative?
Hello,
Go for microsoft Visio. Download a trial version from below URL:-
http://office.microsoft.com/en-us/visio/default.aspx
Thanks
Hari
"WoodenSWord" <WoodenSWord@.discussions.microsoft.com> wrote in message
news:2BE42E3C-457B-47F4-9B48-5909C70BF5FB@.microsoft.com...
> Hello i am looking for a good database modelling tool in order to design
> an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||If you can persuade your company to fork out the cash, I'd recommend ERWIN :
http://www3.ca.com/solutions/Product.aspx?ID=260
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I use Erwin but it is really expensive for most people. ER/Studio from
Embarcadero is also very good and much cheaper -
http://www.embarcadero.com/products/erstudio/index.html.
Roman
"WoodenSWord" wrote:

> Hello i am looking for a good database modelling tool in order to design an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||We can 2nd that. We're a small agency and find er-studio almost
indespensible.
The developers are always after me for a new 'database map' ...
It's helpful to have a large format plotter around if you're going to be
creating systems with hundreds of tables and relations. Or current just
barely readable er-studio map for our water rights system measures roughly 3
x 4 feet.
Barry
in Oregon
"Roman Rehak" <RomanRehak@.discussions.microsoft.com> wrote in message
news:B79EFCE6-677E-4AD9-88A8-1579587F15BD@.microsoft.com...[vbcol=seagreen]
> I use Erwin but it is really expensive for most people. ER/Studio from
> Embarcadero is also very good and much cheaper -
> http://www.embarcadero.com/products/erstudio/index.html.
> Roman
> "WoodenSWord" wrote:

Thursday, March 22, 2012

A view of two tables

A view of two tables

Monday, March 19, 2012

A to Z table data

Sunday, March 11, 2012

A Special Order By clause?

Tuesday, March 6, 2012

A Referense All Query

Thursday, February 9, 2012

A good Database Modelling Tool for Sql Server 2005

SQL server questions

Blog Archive

About Me