swansonplace
asked on
HOw to create sql statement?
Is there any way to create the output results given the input values:
Input table Merchants:
Unique Merchant No. Date
12345 2/9/2006 10:18PM
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
Output Case 1:
Unique Merchant No. Date Created Column
12345 2/9/2006 10:18PM 2
12345 2/9/2006 9:31PM 1
67890 5/8/2007 5:00PM 1
Output Case 2: Notice that I am displaying only columns with a 1 in the front or items that
were created first
Unique Merchant No. Date Created Column
12345 2/9/2006 9:31PM 1
67890 5/8/2007 5:00PM 1
The true objective is to come up with the output displayed below:
Output Objective: Notice that I am displaying only columns with a 1 in the front or items that
were created first
Unique Merchant No. Date
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
Input table Merchants:
Unique Merchant No. Date
12345 2/9/2006 10:18PM
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
Output Case 1:
Unique Merchant No. Date Created Column
12345 2/9/2006 10:18PM 2
12345 2/9/2006 9:31PM 1
67890 5/8/2007 5:00PM 1
Output Case 2: Notice that I am displaying only columns with a 1 in the front or items that
were created first
Unique Merchant No. Date Created Column
12345 2/9/2006 9:31PM 1
67890 5/8/2007 5:00PM 1
The true objective is to come up with the output displayed below:
Output Objective: Notice that I am displaying only columns with a 1 in the front or items that
were created first
Unique Merchant No. Date
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
ASKER
I have not figured out how to create the tables from any of the output cases.
But I have got this so far could you point me in the right direction:
select top 1
select MerchantNo, dDate from merchants
order by MerchantNo, dDate desc
where
(value = select distinct MerchantNo from merchants)
how to i loop through every single distinct Merchant No. The thing is this:
1. I perform a select on each distinct MerchantNo where the merchant number is used to create another
table that has the merchantNo and date for the particular mechantno I am look at. Then,
out of that set I get the top 1. The trick is to perform each merchantNo seperately, get the result, and then go to the next merchant number.
Let me know if you have any ideas. I really like this one. Makes you think a little. Like a puzzle.
But I have got this so far could you point me in the right direction:
select top 1
select MerchantNo, dDate from merchants
order by MerchantNo, dDate desc
where
(value = select distinct MerchantNo from merchants)
how to i loop through every single distinct Merchant No. The thing is this:
1. I perform a select on each distinct MerchantNo where the merchant number is used to create another
table that has the merchantNo and date for the particular mechantno I am look at. Then,
out of that set I get the top 1. The trick is to perform each merchantNo seperately, get the result, and then go to the next merchant number.
Let me know if you have any ideas. I really like this one. Makes you think a little. Like a puzzle.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I stil don't see how you created t1 and t2.
ASKER
How do I create a table to work from:
select * from (Merchants as M, I would like to have another table here that I create as NM)
where M.date = select(max(M.date)) and
M.merchantNo = NM.merchantNo
This other table would also use the same Merchants table but it would return this:
select distinct merchantNo from merchants.
--------------------------
Input table Merchants:
Unique Merchant No. Date
12345 2/9/2006 10:18PM
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
Output Objective: Notice that I am displaying only that were created first
Unique Merchant No. Date
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
select * from (Merchants as M, I would like to have another table here that I create as NM)
where M.date = select(max(M.date)) and
M.merchantNo = NM.merchantNo
This other table would also use the same Merchants table but it would return this:
select distinct merchantNo from merchants.
--------------------------
Input table Merchants:
Unique Merchant No. Date
12345 2/9/2006 10:18PM
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
Output Objective: Notice that I am displaying only that were created first
Unique Merchant No. Date
12345 2/9/2006 9:31PM
67890 5/8/2007 5:00PM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the great help.
where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantN