Solved

Selecting distinct

Posted on 2008-10-31
3
215 Views
Last Modified: 2012-05-05
Hello all
I have a view in my SQL Express database that looks like this
"SELECT     Stock_Code, Pallet_No, Weight, daterep, Trans, PONo
FROM         dbo.VW_SUPEXUSAGE01
WHERE     (daterep >= CONVERT(DATETIME, '22/10/2007', 103))"

This works fine but is there a way of creating the same view but making it pick up a single/disitnct pallet_no . There can be multiple pallets but i only want to pick up the first instance

Many thanks in advance
0
Comment
Question by:bostonste
3 Comments
 
LVL 8

Accepted Solution

by:
eszaq earned 500 total points
ID: 22848513
Not sure what you are trying to achieve...

You might need to use WHERE clause (to select one particular Pallet_No):
"SELECT     Stock_Code, Pallet_No, Weight, daterep, Trans, PONo
FROM         dbo.VW_SUPEXUSAGE01
WHERE     (daterep >= CONVERT(DATETIME, '22/10/2007', 103))
   AND Pallet_No = ?yourVariableValue?"

OR you might need to use GROUP BY:
"SELECT     Pallet_No, Stock_Code, Weight, daterep, Trans, PONo
FROM         dbo.VW_SUPEXUSAGE01
WHERE     (daterep >= CONVERT(DATETIME, '22/10/2007', 103))
GROUP BY Pallet_No"

You can group by more then one column, but remember that columns in SELECT must be listed in the same exact order as in GROUP BY.

But the way you put your question it seems you need the first solution - WHERE clause.
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 22848515
SELECT     Distinct Pallet_No, StockCode, Weight, daterep, Trans, PONo
FROM         dbo.VW_SUPEXUSAGE01
WHERE     (daterep >= CONVERT(DATETIME, '22/10/2007', 103))
0
 

Author Closing Comment

by:bostonste
ID: 31511973
ESZAQ
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
How to convert JSON file to csv? 7 54
c# code 19 61
SQL Script to find duplicates 16 20
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now