Select Unique Country / Date Pairs With Self Join

Sep 16, 2013

I have built a sample table, query, and results for this question. I am using SQL server.

declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX

[Code] ....

Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2

I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.

My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.

1) Is this possible to do with a self join?

View 2 Replies


ADVERTISEMENT

Transact SQL :: Unique Pairs Of Data?

Sep 23, 2015

In my table, I have some duplicate rows. I want to select only the unique value pairs.

So, the table/data look like this:

ColumnA  -- ColumnB
123  -- 234
234  -- 345
234  -- 345
345  -- 456

I want to create a select statement that will only return:

123  -- 234
234  -- 345
345  -- 456

In other words, the duplicate row i.e. the second 234 - 345 -- won't be included in the set my SELECT statement will return.

View 2 Replies View Related

Select Latest Row When ID Has One Or More Rows And Each ID Unique From And To Date

Dec 8, 2013

I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).

ID
NAME
FROM_DATE
TO_DATE

[code]...

View 1 Replies View Related

T-SQL (SS2K8) :: Select Query - Unique Orders For Date Range?

Aug 27, 2014

I have an Orders table which has the following fields:

OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)

(and other irrelevant fields)

Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.

View 5 Replies View Related

How To Select Unique Row When Entire Row Not Unique?

Mar 12, 2008

I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code



Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword





and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0

This could return multiple part numbers and If they have entered notes I want the row with the notes

Thank You
Dominic Mancl

View 1 Replies View Related

Selecting Unique Rows In A Join

Mar 20, 2008

I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM         gf_game_ext AS ext INNER JOIN                      gf_game ON gf_game.gameID = ext.gameIDWHERE     (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot! 

View 9 Replies View Related

Help With A Join That Needs To Return Unique Records

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 11 Replies View Related

Join To Show One Row Per Unique Record

Apr 23, 2015

I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.

Tables include:-

1. REQ
2. RFQ
3. PO
4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO)
5. DOCUMENT_STATUS (contains status of document, REQ_CREATE)
6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT)
7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)

Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.

I realised the multiple lines generated due to the following:-

One to many relationships:
A. RFQ - 1 or more PO
B. PO - 1 or more PO_REVISON

I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.

View 13 Replies View Related

Help With A Join To Return Unique Values

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 7 Replies View Related

Parsing CSV Name=value Pairs Into Columns

Nov 7, 2006

Hello smart SQL people,

I have a field in the DB that contains name value pairs in the form of csv. Is there a neat trick (using a stored proc) to get it to be diplayed as in columns (maybe create a view around it or somthing?) ?

EX.

Select 'name1=val1;name2=val2;name3=val3' as MyData

--> Returns
MyData
-----------
name1=val1;name2=val2;name3=val3

============================================

How can i get it to return

name1 name2 name3
--------------------------------
val1 val2 val3

View 9 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?

View 4 Replies View Related

T-SQL (SS2K8) :: Convert Unique Identifier From String To Use In A Join / Where Clause

Jan 6, 2015

I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?

SELECTCASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE RTRIM([program_name])
END ProgramName
, Val1.UqID
, Val1.UqIDStr

[Code] ......

View 6 Replies View Related

Storing Large Arrays Of Ordered Pairs

May 13, 2008

How do you all recommend storing ordered pairs in SQL Server 2005? I plan to add one record for every data point but this will generate many records and requires an extra field to relate the points together. Are there any better ways to do this? Can the data still be searchable or does it have to be unpacked first?

View 2 Replies View Related

String Parsing - Grab Some Key Value Pairs From Text In Column

Jul 21, 2014

I want to grab some key value pairs from the text in sql column

e.g.

some text[Key1=Val1]some text[Key2=Val2][Key3=Val3]some text

I want a function which takes Keyname as input & returns the value related with it if found.

GetValueFmKey('Key1') should return Val1 and like on.

View 1 Replies View Related

Reconcile Pairs Of Parent-Child Items. Ideas Please?

Feb 15, 2008

I need ideas on how to achieve the following:

A parent can have many children - always has at least one.

A parent always has an 'anti-parent' with an equal number of 'anti-children' to the normal parent

Each child and anti child has a unique ID


I need to reconcile each Parent/antiParent Child/antichild and highlight the differences before.

Normally, reconciliation reports are fairly straightforward due to the fixed number of items, I'm not quite sure of the best way to do this.


How it should look in the report

PARENT A field1,field2,...,field n
PARENT B field1,field2,...,field n
CHILD A1 childfield1,cf2,....cf n (child A1 and B1 share a unique ID)
CHILD B1 childfield1,cf2.....cf n
CHILD A2
CHILD B2
...
CHILD An
CHILD Bn


So, compare PARENT A with PARENT B and highlight any differences
compare CHILDA1 with CHILD B1 and highlight any differences
...
compare CHILDAn with CHILD Bn and highlight any differences

Thoughts?

View 2 Replies View Related

Select Unique

Aug 25, 2005

We have a transaction record that, for the sake of brevity, I will usea simple paradigm to convey my need:A sales clerk sells several pairs of shoes over the period of a day,each & every day the clerk works. Each transaction is recorded ina database. All clerks use a single physical register but login tocapture unique ID. As each clerk logs in, it automatically logs outthe prior user and writes a zero-dollar record.Each record logs the RcdNbr, clerk's ID, date-timestamp and salesamount. (Other fields not pertinent to this discussion)Example:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00The above is unsorted. I would first sort by CID (ClerkID) to get:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00As a double-check of a clerk's actual worked hours we want to extractthe last record for each clerk for each day. In the above sample, I'dneed records 004 & 006.The transaction file covers 6 months worth of data.Is there a statement that I can construct that would extract the last(greatest?) time for each clerk for each day?

View 1 Replies View Related

Unique Select

Dec 19, 2007

Hi,
There is a table called tblPrices with fields
Security_ID int
Bid_Price decimal(12, 4)
Ask_Price decimal(12, 4)
Price_Quote_Date smalldatetime
In this table there are thousands of records. For each Security_ID, there are several records with different Price_Quote_Dates
What I would like to have is:
A select query which shows every single Security_ID with the prices for the LATEST date that there is a price for that Security_ID
Example:
1,100, 102.32, 1/12/2005
1, 43, 76.33, 6/12/2005
2,65.77, 45.76, 12/12/2007
...
...
...
Result
1, 43, 76.33, 6/12/2005
2,65.77, 45.76, 12/12/2007
...
...
...

This is what I have started but it is not correct yet because the Security_IDs are repeated
select
Security_ID,
Bid_Price,
Ask_Price,
max(Price_Quote_Date)
from
tblPrices
group by
Security_ID,
Bid_Price,
Ask_Price

View 6 Replies View Related

Unique Date Conversion Problem

Nov 16, 2001

Hello,

We have a field that is defined as a string which is used to store a date.
The format of the date can be any interpretation of a date as long as it follows the sequence of day/month/year. Insomuch as,

12/10/2000 is 12th October 2000 as well as 12/10/00

Because the yyyy date can be 2000 or 00 we are having problems with the
following syntax:

SELECT Convert(Datetime, myproblemfield, 103) As YetAnotherProblem
FROM SomeTable
WHERE YetAnotherProblem < #12/10/2000#

Any whiz kids out there who can help.

Send an e-mail to David.Butcher@SwissLife.co.uk & Adrian.Murphy@SwissLife.co.uk

View 1 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

This Is Bat Country

Apr 2, 2008

Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.

Clint




US Navy - fueled, armed, and go for launch.

View 7 Replies View Related

Running Sum Is A Unique ID Field - Balance On Certain Date?

Dec 20, 2012

I am working on a software primarily related to accounting with visual basic

Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg

ID DATE DEBIT CREDIT BALANCE

1 02.02.2012 100.00 0.00 100.00

6 04.04.2012 0.00 150.00 -50.00

3 02.05.2012 70.00 0.00 20.00

4 02.05.2012 80.00 0.00 100.00

2 06.06.2012 120.00 0.00 220.00

must balance the figure above.

if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

View 1 Replies View Related

Select Unique Rows For All Columns

May 17, 2007

Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.

Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows

Any thoughts?


Jorge3921

View 14 Replies View Related

Continents To Country

Mar 2, 2007

I have the country iso table does anyone have the data for tying contenents to the countries?


You can do anything at www.zombo.com

View 5 Replies View Related

Query For Ip And Associated Country

Dec 28, 2007

Hi,

I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP

2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name

I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"

Thanks

Denis

www.tabletennis.gr

View 20 Replies View Related

Date Query - Produce Unique Line By Month

Oct 21, 2013

Aim –To produce a unique line by month for the FDMSAccountNo

I have created the following query (see below) which produces the following result set

FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000

SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate

[Code] ....

Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08

[Code] ....

View 1 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

How To Use Distinct To Select Unique Records But Without Sorting ?

May 30, 2008



Hi

I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.

Please suggest me the solution for this problem.

Thanks

Regards

Avinash Vyas

View 4 Replies View Related

SQL 2012 :: Fetch Only Country Name

Nov 18, 2014

I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.

create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')

[code]...

View 7 Replies View Related

Know The Country Version Of SQL Server

Jul 20, 2005

Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye

View 1 Replies View Related

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records

Apr 22, 2006

I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View 3 Replies View Related

How Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result

Mar 7, 2006

Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie

View 1 Replies View Related

Transact SQL :: Select Unique From Table If Specific Value Does Not Exists

Jul 31, 2015

I have a table that has for each shop a value that can change over time.For example

BK_POS 1 --> Segment A
BK_POS 1 --> Segment /

What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".

View 3 Replies View Related

Transact SQL :: SELECT Unique Values In Single Column?

Jun 8, 2015

I have the following two tables...

tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress

I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved