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                      











swansonplaceAsked:
Who is Participating?
 
sas13Commented:
select distinct m.merchantNo, m.date
from Merchants m
where m.date = (select min(date) from Merchants where merchantNo=m.merchantNo)
0
 
dqmqCommented:
Select * from YourTable T1
 where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)
0
 
swansonplaceAuthor Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
dqmqCommented:
>how to i loop through every single distinct Merchant No.

You don't.  With SQL set processing you do everything at once.  

Let's suppose this creates the  result set you desire:

Select *
from YourTable T1
where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)


Then you can make that into a table like this:

Select *
from YourTable T1
INTO NEWTABLE
where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)







0
 
swansonplaceAuthor Commented:
Sorry, I stil don't see how you created t1 and t2.
0
 
swansonplaceAuthor Commented:
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                      


0
 
swansonplaceAuthor Commented:
Thank you for the great help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.