December 16, 2013 at 3:35 pm
Hello people. I'm fairly new to SQL so my apologies for not understanding some of the basics.
I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables. Can anyone help me out? Thanks for any information.
Robert
December 16, 2013 at 4:15 pm
Welcome to the forum! First please read the article in my signature and provide the information it talks about (create table statements, insert data statements and expected output).
With that said I can try and guess what you are looking. I think what you need is to do a join on the three tables on column 1:
from dbo.Table1 a
join dbo.Table2 b on a.Column1 = b.Column1
join dbo.Table3 c on a.Column1 = c.Column1 This will get you the three prices side-by-side from there you could group by the first column and return the min price.
This isn't a great answer, but if you provide the data that I mention above someone will get you the correct query.
December 16, 2013 at 5:28 pm
Here's another suggestion:
WITH Table1 (ID, Val) AS
(
SELECT 201220, 0.5
UNION ALL SELECT 201219, 0.4
UNION ALL SELECT 201218, 0.4
)
,Table2 (ID, Val) AS
(
SELECT 201220, 0.9
UNION ALL SELECT 201219, 0.3
)
,Table3 (ID, Val) AS
(
SELECT 201220, 0.2
UNION ALL SELECT 201219, 0.6
)
SELECT ID, Val=MIN(Val)
FROM
(
SELECT ID, Val
FROM Table1
UNION ALL
SELECT ID, Val
FROM Table2
UNION ALL
SELECT ID, Val
FROM Table3
) a
GROUP BY ID;
It will work differently than what Keith suggested in the event some of the IDs appear in one of the table(s) but not in the other(s). For example, ID 201218 appears in only one table and its cost is returned.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 16, 2013 at 5:39 pm
OK, the second query I don't quite understand. It looks like I would have to hand code each ID.
December 16, 2013 at 5:42 pm
robert.l.craig (12/16/2013)
OK, the second query I don't quite understand. It looks like I would have to hand code each ID.
Are you perhaps confusing the sample data I set up in CTEs Table1, Table2 and Table3? Just substitute your table names between the UNION ALL statements and get rid of the 3 CTEs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 16, 2013 at 5:48 pm
OK, so something like this?
WITH Table1 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
,Table2 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
,Table3 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
SELECT ID, Val=MIN(Val)
FROM
(
SELECT ID, Val
FROM Table1
UNION ALL
SELECT ID, Val
FROM Table2
UNION ALL
SELECT ID, Val
FROM Table3
) a
GROUP BY ID;
December 16, 2013 at 6:08 pm
December 16, 2013 at 6:15 pm
OK, sorry about that.
table1
prefix cost
201220 0.50
201221 0.49
table2
prefix cost
201220 0.49
201221 0.52
table3
prefix cost
201220 0.46
201221 0.49
The ultimate goal is to display all records, but before doing that, I only want the lowest of each row. In other words, for 201220, compare the cost of that to the other tables, throw out the higher two of the three, then display 201220 and 0.46. I don't want multiples because the goal is to get a report of the lowest cost of each prefix. There is a chance that not all tables have identical prefixes (some tables have the same ones, some don't have it at all). Does that help?
December 16, 2013 at 6:29 pm
robert.l.craig (12/16/2013)
OK, so something like this?WITH Table1 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
,Table2 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
,Table3 (ID, Val) AS
(
SELECT prefix, cost
UNION ALL SELECT prefix, cost
)
SELECT ID, Val=MIN(Val)
FROM
(
SELECT ID, Val
FROM Table1
UNION ALL
SELECT ID, Val
FROM Table2
UNION ALL
SELECT ID, Val
FROM Table3
) a
GROUP BY ID;
More like this:
SELECT prefix, Val=MIN(cost)
FROM
(
SELECT prefix, cost
FROM Table1
UNION ALL
SELECT prefix, cost
FROM Table2
UNION ALL
SELECT prefix, cost
FROM Table3
) a
GROUP BY prefix;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 16, 2013 at 6:31 pm
December 16, 2013 at 6:38 pm
Nope. Just need to spit out the list based on those factors into a CSV file and I'm going to apply a simple markup in excel. Of course, doing that with the query would be awesome, but I don't want to be too needy.
March 2, 2014 at 7:16 am
OK, so the dynamics of this query have changed. While it serves its purpose, I'd like to view the data in a different way. Instead of just telling me what the lowest rate is by code, I'd like to see the code, rate, and source (table name) it came from. So, if I upload 3-5 lists, I'd like see a report with the code on the left, rate next to it, and each column represents the table it came from. Is it relatively easy to modify the below query to do that?
SELECT prefix, Val=MIN(rate)
FROM
(
SELECT prefix, rate
FROM dbo.data1
UNION ALL
SELECT prefix, rate
FROM dbo.data2
UNION All
SELECT prefix, rate
FROM dbo.data3
) a
GROUP BY prefix;
March 2, 2014 at 5:11 pm
You probably need something like this:
WITH ConsolidatedTables AS
(
SELECT prefix, rate, T='data1'
FROM dbo.data1
UNION ALL
SELECT prefix, rate, 'data2'
FROM dbo.data2
UNION All
SELECT prefix, rate, 'data3'
FROM dbo.data3
)
SELECT prefix, rate, T
FROM
(
SELECT prefix, rate, T, rn=ROW_NUMBER() OVER (PARTITION BY prefix ORDER BY rate)
FROM ConsolidatedTables
) a
WHERE rn=1;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply