Filtering Out Duplicate Rows Based On Three Column
Nov 25, 2014
I want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query
;With Temp as (
SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,
[code]...
In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.
View 8 Replies
ADVERTISEMENT
Nov 30, 2007
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
View 7 Replies
View Related
Sep 22, 2015
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
View 2 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
Mar 12, 2013
I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price
Example:
Cheetos
Zone A: $2.79
Zone B: $2.89
The pricing data in the tables is listed by zone. However, the new product uses pricing by store.
Zone A contains stores 1,2,4,6,7....
Zone B contains stores 10,11,12,14.....
I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.
So I'm looking to go from:
Zone UPC Price
A 1234500000 2.79
B 1234500000 2.89
To:
Store UPC Price
1 1234500000 2.79
2 1234500000 2.79
10 1234500000 2.89
11 1234500000 2.89
View 13 Replies
View Related
Feb 13, 2014
I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.
Select
Distinct
TestID,
TestDate,
TestName
From third.test
I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....
View 1 Replies
View Related
Mar 13, 2006
Hello,
I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?
I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example
EdgeID Desc TermType ReltTotID
1Global Edge Model w/ Fwd Earn II T 1
2Short Term Global Edge Model w Fwd Earn IIS 1
3Long Term Global Edge Model w Fwd Earn IIL 1
4Emerging Market Edge Model w Fwd Earn T 4
5Short Term EM Edge Model w Fwd Earn S 4
6Long Term EM Edge Model w Fwd Earn L 4
19SmallCap Edge Model w/ Fwd Earn T 19
20SmallCap Short Term Edge Model w/ Fwd EarnS 19
21SmallCap Long Term Edge Model w/ Fwd EarnL 19
35Global+EM Edge Model w Fwd Earn T 35
The final query result is :
EdgeID Description Short Desc PerID UnivID DefID
1Global Edge Global Developed 500622355938
4Emerging Market Emerging Markets 500632356039
19SmallCap Edge Small Cap Edge 500642364244
I would like it to be :
1Global Edge Global Developed 500622355938
2Short Term Global Developed NULL2355938
3Long TermGlobal Developed NULL2355938
4Emerging Market Emerging Markets 500632356039
5Short Term Emerging Markets NULL2356039
6Long Term Emerging Markets NULL2356039
19SmallCap Edge Small Cap Edge 500642364244
19Short Term Small Cap Edge NULL2364244
19Long Term Small Cap Edge NULL2364244
The stored proc query is as below:
SELECT
EdgeModelID = em.EdgeModelID
--, EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID
FROM OptMod..GO_EdgeModels em
JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1
JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'
LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID
LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'
WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID
Thanks in advance!!!
sqlnovice123
View 1 Replies
View Related
Sep 6, 2011
I have a table with two columns refid and name and it has the following values
1 tom
1 jim
2 bob
1 bob
I need a resultset that would have the following values
1 tom, jim, bob
2 bob
I have tried couple of things one being:
DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist
But I am looking for a resultset with a unique refid and all the names comma separated for that refid.
View 2 Replies
View Related
Sep 5, 2014
I need to split a row into multiple rows based on multiple column values.
time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00
I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
row 1
time_in 10:00
time_out 12:00
row 2
time_in 13:00
time_out 14:00
row 3
time_in 15:00
time_out 16:00
View 2 Replies
View Related
Dec 17, 2007
I have a table that holds a number of offers made to a orginization for placements at a lecture. what im wanting to do is have each of the rows for a orginization repeated so that the names of people attending can be put into the database.
The first table would looks something like this:
id___|__orginization_| lecture | offers
1 | orga | lec1 | 5
2 | orgb | lec1 | 3
3 | orga | lec2 | 3
the result im looking to get is something like this where the name of the attendess would be inputed in an application.
id | orginization | lecture | nameofattende
1 | orga | lec1 | j. blog
2 | orga | lec1 | s. smith
3 | orga | lec1 | h. samual
4 | orga | lec1 | j. sams
5 | orga | lec1 | b.j. james
6 | orgb | lec1 | m. curry
7 | orgb | lec1 | k. murry
8 | orgb | lec1 | g. hansen
Can anyone help with this
cheers
colin
View 4 Replies
View Related
Sep 23, 2015
Here's the my structure and data as follows
>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans
[Code] ...
Now requirement is : -
tranno medrecno
createdon createdat
nooftrans tranno medrecno
createdon createdat
nooftrans
[Code] ....
View 4 Replies
View Related
Apr 30, 2015
I have a query that gives me a result with a column value for example 4.
I now want to repeat this row 4 times with a new column that calculated from 1 - 4.
Or when column value is 3 I want to repeat row 3 times with new column name 1-3
View 7 Replies
View Related
Jul 23, 2005
Hi, all:I'm having trouble with something that probably has a simple solution.I have linking tables that can list a particular MemberID multipletimes. Is there a way to run a query so that a specific ID will showup once?Here is an example of the tables I've set up --MemberTable:==MemberIDMemberName1Dave2John3MichaelFruitTable:==FruitIDFruitName1Apple2Orange3PearVeggieTable:==VeggieIDVeggieName1Carrot2Celery3Potato....and these linking tables --Members2Fruits:==MemberIDFruitID1213223132Members2Veggies:==MemberIDVeggieID1221222331This is the query I'm using to retrieve the ID's:SELECT distinct m.*, m2f.*, m2v.*FROM ((MemberTable m INNER JOIN Members2Fruits m2fON m.MemberID = m2f.MemberID)INNER JOIN Members2Veggies m2v ON m.MemberID = m2v.MemberID)WHERE ...By the way, I know of the GROUP BY clause, but it didn't work for me.Thanks for any help.J
View 2 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Jul 3, 2001
hi,
I am trying to fetch data from 2 tables, say TABLE1 and TABLE2, both of which got columns like id and num. Then i want all the rows from TABLE1 where id1=id2 and num1 != num2.
but it is showing all the rows for an id1 twice, if there are two records in TABLE2 with same id and num.
is there any way to filter those records without using the distinct keyword.
regards
Rajeev.
View 1 Replies
View Related
May 2, 2008
Hello,
I have a dataset which I would like to remove data from, but I can't seem to find out how to do this.
The dataset contains the following columns:
SCode, NIn, SIn, AIn, NOut, SOut, AOut and TagNumber.
I would like to remove data from the dataset when the following occurs:
("SC123", "NIn123", "s-in-323", "a-in-342", "NOut43", "s-out-231", "a-out-45", "tagnumber12")
("SC123", "NIn123", "s-in-xyz", "a-in-xws", NULL, NULL, NULL, "tagnumber12")
This is when NIn occurs with the same value more than once, and I would like to remove (or ignore, filter) the row when NOut, SOut and AOut are null.
I am new to SSIS and can't see how I could do this (although I'm sure it's possible).
If anyone could show me how I would appreciate it.
Thanks.
View 11 Replies
View Related
Oct 21, 2013
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
View 8 Replies
View Related
May 2, 2014
I am new in SQL programming world, following is query that i had created
select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime
from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid
where netflowsources.LastTime NOT LIKE GETDATE()
which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example
nodeid value = a,a,a,b,b,c,c,c,c,d,d,d
But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.
View 9 Replies
View Related
Oct 18, 2001
There's some SQL below (T-SQL) & I'm wanting to have this result set
grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate
values. I only want to get rows for one instance of the Venue_ID (per
BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks,
Darren
darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID,
Booking_Header.Booking_Header_Description,
Booking_Header.BStat_ID, Booking_Header.BT_ID,
Booking_Header.Tagged, Booking_Header.Status_Timestamp,
Booking_Header.Start_Date, Booking_Header.Days_Qty,
Proposal.PPL_ID, Proposal.PPL_Status,
Booking_Component.BCOM_ID,
Booking_Component.Component_Description,
Booking_Component.Venue_ID, Venue.Venue_Code,
Venue.Description, Address.Address_ID, Address.Town,
Booking_Status.BStat_Description,
Booking_Type.Type_Description
FROM dbo.Booking_Header INNER JOIN
dbo.Proposal ON
dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN
dbo.Booking_Component ON
dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER
JOIN
dbo.Venue ON
dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN
dbo.Address ON
dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN
dbo.Booking_Status ON
dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER
JOIN
dbo.Booking_Type ON
dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID
WHERE (dbo.Proposal.PPL_Status = 1) AND
(dbo.Booking_Header.BH_ID = 10)
Thanks,
Darren
View 2 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Jun 2, 2015
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
View 5 Replies
View Related
Feb 14, 2005
While using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day?
The easiest way I've found of doing so is :
Code:
SELECT *
FROM MyTable
WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00'))
To me that seems a bit complicated... but it works...
Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well)
-MBirchmeier
View 5 Replies
View Related
May 24, 2006
I have a few tables that have an disabled attribute using a BIT datatype. A lot of my queries on the front end look like:
SELECT *
FROM TableA
WHERE disabled <> 1
There's usually some other constraints on the query (get TOP 10 and greater than a certain date for example). Right now my tables are very small (only a couple thousand rows). I don't anticipate these tables having more than 100,000 rows.
Right now let's say there's only a CLUSTERED INDEX on the date field, and regular INDEXES on the identity field and perhaps some other UNIQUE name in the table.
Unless I am doing ranged queries on the CLUSTERED INDEXED field, I'm going to be performing table scans almost every time, right?
This sort of goes along with another question:
Say you run the following (SQL Server):
CREATE TABLE TestA (
[id] INT IDENTITY (1, 1) PRIMARY KEY,
disabled BIT DEFAULT 0
)
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
Since [id] is a PK there will be a CLUSTERED INDEX placed on it. My question is; what does the optimizer do when you perform the following query?
SELECT TOP 3 *
FROM TestA
WHERE disabled <> '1'
My assumption is that since there's a CLUSTERED INDEX it will simply iterate through every tuple and check to see if disabled is not '1'. If my assumption is correct then these kind of boolean fields aren't a big deal if TOP queries are performed on a CLUSTERED INDEX.
So I guess what I am getting at is: Are bit attributes a sign of bad design? As tables get larger will performance degrade significantly? Would a better design be to have a seperate table of disabled items (which may result in large NOT IN subqueries)?
Any information on his would be greatly appreciated.
View 1 Replies
View Related
May 25, 2000
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
View 3 Replies
View Related
Mar 10, 2008
Is this level of security possible in RS 2005? if so how?
Any guidance would be appreciated.
Thanks.
View 1 Replies
View Related
Dec 28, 2007
Hi,
Im trying to create a drop down parameter whereby if i select a certain field, a different dropdown will be filtered off only the relevant selections, is this possible.
View 7 Replies
View Related
Jun 6, 2006
Hi,
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
Thanks in advance!!!
sqlnovice123
View 2 Replies
View Related
Jun 15, 2001
to any who can help:
Here are some rows in a table with their lettered columns:
A B C D E
012345Ae2001-01-01 00:00:00.0002001-01-02 00:00:00.0000
012345Ae2001-01-02 00:00:00.0002001-01-03 00:00:00.0000
012345Ae2001-01-03 00:00:00.0002001-01-04 00:00:00.0000
012345Ae2001-01-04 00:00:00.0002001-01-05 00:00:00.0000
012345Ae2001-01-19 00:00:00.0002001-01-20 00:00:00.0000
012345Ae2001-01-20 00:00:00.0002001-01-21 00:00:00.0000
012345Ae2001-01-24 00:00:00.0002001-01-25 00:00:00.0000
012345Ae2001-01-25 00:00:00.0002001-01-26 00:00:00.0000
012345Ae2001-01-25 00:00:00.0002001-01-26 00:00:00.0001
012345Ae2001-01-26 00:00:00.0002001-01-27 00:00:00.0000
if you notice on the 8 and 9th rows the only difference between them is in
the E column(0 and 1). What I am trying to do here is to display all with
max(E). So in the above example, I should display rows 1-7,9,10 (8th row
will not display because the 9th row has 1 in the E column). this is the
query I have been using on SQL Server 2000 but I keep on displaying all the
rows:
SELECT A,B,C,D,max(E)
FROM <table>
WHERE ( A = '012345A' ) AND
( B >= '01/01/2001' ) AND
( C <= '01/31/2001' )
GROUP BY A,
B,
C,
D
any solutions?
TIA
View 1 Replies
View Related
Jul 31, 2015
Data Scenario – Sitemaster entity has two attributes (Region and Country).
Region and Country are both domain-based attributes on separate entities [Region and . The Country entity has a domain based attribute on REGION.
Data relationship looks like this… REGION > COUNTRY
Customer Requirement
Customer would like the MDS UI to filter the Country drop-down options based on the Region selection.
To my knowledge – the only filtering that can be done is based on user security using Hierarchy Membership Permissions.
View 2 Replies
View Related
Feb 2, 2008
Can anyone help me understand how to come up with the correct data? I have a table with sales orders. If one is put in incorrectly another one is inserted to back it out, then a third is inserted. Here is a sample:
Sales# Destination Amount
1234 Akron 100
1234 Akron -100
1234 Cleavland 125
The correct result set would be "1234, Cleavland, 125."
How do I script that?
Thanks!
View 5 Replies
View Related
Nov 13, 2006
Hi.
I am using Visual Studio 2005 professional and SQL Server 2005 Express Edition.
I am having a bit of an issue at the moment with my project. I have a large SQL server database (over 600,000 rows of data decoded from a text datafile and stored by my C# program) where I need to select a series of distinct rows.
I have a datacolumn of varchar(10) called UID which is assigned to every row in my database. It's not a unique code so it can happen several times. By selecting a specific UID code, I can narrow down my selection to a specific range of rows (which are incremental in order). This range will contain many names.
What I want to do is find a UID which will have one of two names. I have so far done this:
SELECT DISTINCT F1.UID FROM MyDataBase F1
INNER JOIN MyDataBase F2 ON
F1.LineIndex < F2.LineIndex AND
F1.UID = F2.UID AND
F1.Names IN (Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda') AND
F2.Names IN ('Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda')
This will return any UID which contains at least two of these names. The LineIndex ensures that the comparison doesn't happen on the same row of data (it's a column set to type int with IDENTITY(1,1) which is also my PRIMARY KEY). This is partially what I want, the selection of UID rows which will have at least two names from the list. Also, the same name will never appear twice in the same UID list.
Now, the problem I am facing is that I want a select a UID range with names in the order I have specified, so if I want a UID list where the names I want are Peter and Linda, I only want to return a set of UID rows where Peter appears earlier in the returned rows than Linda.
It is possible that other UIDs have the same names, but in reverse order, starting Linda, Alan, David etc...John, Bob. I would want to discard these entries.
Can anyone please give me any hints as to how I could get around this?
Thanks in advance,
Sean
View 5 Replies
View Related
Sep 10, 2015
I am new to SQL,I wrting a query which I will at the later stage create a report but my query is creating duplicate records,I have tried the group by and the sub selects but this still bring all uneccessary columns I do not need,
I have a query that goes
Select
name
Date from
date from
date to
summary
Estimated value
I know in this database i only have 1 record containing that information but immediately when i ass the estimated value field a lot of duplicate,I have tried using group by the same thing happens,or even when i tried having count then all the columns disappear.
View 3 Replies
View Related
Nov 28, 2007
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies
View Related
