How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Select Rows Based On One Distinct Column

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

Transact SQL :: Strategy To Translate Column Data Into Distinct Rows

Aug 27, 2015

I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:

PrimaryID       SKUChange              DateChange         LocationIdChange        StateChange
10003             TRUE                       FALSE                  TRUE                          FALSE
etc...

The output I'd like to see would be like this:

PrimaryID        Field Changed          Previous Value      New Value
10003             SKUName                 SKU12345           SKU56789
10003             LocationId                 Den123               NYC987
etc...

The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.

View 3 Replies View Related

SQL Counting Number Of Non-distinct Rows?

May 3, 2006

Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks

View 3 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View 2 Replies View Related

Insert Rows Based On Number Of Distinct Values In Another Table?

May 21, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

how can I update Table 1 to get desired results?

View 2 Replies View Related

SQL Server 2012 :: Insert Rows Based On Number Of Distinct Values In Another Table

May 20, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)

Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

How can I update Table 1 to see desired results?

View 3 Replies View Related

SQL 2012 :: Get Total Number Of Each Distinct Value In Column And Write To Another Table

Jul 14, 2014

How can i get the total number of each distinct value in the column and write it to another table e.g.

MyTable

Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple

Result

Fruit Count
Apple 3
Banana 2
Watermelon 2

View 9 Replies View Related

Select Distinct Rows From Duplicate Rows....

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

Show The Rows Containing Same Values Rate As Column In Html Table

Mar 27, 2008

Hi All,

I have following problem
please help me to resolve it.

There is table for shipping
as follows:




City


Kg


Rate(rs)




Rajkot


1


25.00




Rajkot


2


30.00




Rajkot


3


42.50




Ahemedabad


1


42.50




Ahemedabad


2


55.00




Ahemedabad


3


67.50




Ahemedabad


4


80.00




Goa


1


90.00




Goa


2


105.00




Goa


3


120.00




Gondal


1


25.00




Gondal


2


30.00




Gondal


3


42.50




Morbi


1


25.00




Morbi


2


30.00




Morbi


3


42.50




Gandhinagar


1


42.50




Gandhinagar


2


55.00




Gandhinagar


3


67.50




Gandhinagar


4


80.00




 We need to display
the table in html page using any Asp.Net language.

The result table should be:




City(s)


1


2


3


4




Rajkot,Gondal,Morbi


25.00


30.00


42.50


-




Ahemedabad,Gandhinagar


42.50


55.00


67.50


80.00




Goa


90.00


105.00


120.00


-




Baruch


70.25


80.00


92.00


120.50




 Description:

- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed. 

Reply as soon as possible.

Thanks

View 3 Replies View Related

How Do I Show All Columns In A Matrix Even If There Are No Data Rows Using The Column On A Given Page?

May 31, 2007

I have one column in a matrix component and it has about 7 items, but the only the items which have values on the page appear at the top of that page.



This is for a labratory so the columns are the different Patient Types and the rows are the different Test Mnemonics. If one of the Patient Types is not used in any of the tests on that page, it doesnt show up. How to I make sure all Patient Types show up on every page?



Thank you all.

View 2 Replies View Related

Getting The Right Distinct Rows

Jan 21, 2008

I have a database for a CMS I have made, which has a column called ‘tag’ everytime a page is updated it inserts another row in to the table with the same tag but with a updated date. i use this method so i have a version history
What I want to get out is rows that have distinct tag columns and is also the newest row associated with that ‘tag’.

View 3 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

I have a SQL Query issue you can find in SQL Fiddle

SQL FIDDLE for Demo

My query was like this

For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related

Make Tablix Column Group Show When Filter Returns No Rows

May 20, 2008

I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.

However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.

In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.

Am I just missing a simple option?

Nathan

View 1 Replies View Related

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

Selecting Distinct Rows

Jun 11, 2008

Hi,
I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's.
 can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
  

View 17 Replies View Related

Select Distinct Rows

Mar 19, 2004

Hi,

I'm having a little bit of trouble trying to figure out how to do this query, right now I have:

SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId
FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU
WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null

and it returns something like this:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
1665 -30987 SELECT_OOS11
2433 -30987 SELECT_OOS11
4272 -30984 SELECT11
2436 -30984 SELECT11
2793 -32708 SELECT11


But I only it want it to return 1 record for each ProductID like so:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
4272 -30984 SELECT11
2793 -32708 SELECT11


ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?

Thanks

View 4 Replies View Related

Select Distinct For Different Rows

Jun 5, 2006

I have the following tablecolumns:  [col1], [col2],[col3] and [NAME].I want to select the name column for each row where [col1]='07'.The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.So now, when I execute my query and add the DISTINCT key I still get all the double rows!I hope this explains my problem, help is really appreciated...ow, btw: deleting the double rows is not an option....

View 4 Replies View Related

Selecting Distinct Rows ??

Oct 27, 1998

View 3 Replies View Related

Number Of Rows Depending On A Column Value

Sep 26, 2007



Hi all,
I have a table with artikels and count, sample:

Art Count
------------
12A 3
54G 2
54A 4

I would like to query this table and for each 'count' retrieve one row:
query result:

Art Count
------------
12A 3
12A 3
12A 3
54G 2
54G 2
54A 4
54A 4
54A 4
54A 4



Is this possible?

Thanks, Perry

View 3 Replies View Related

Return Rows That Arent Distinct

May 5, 2008

I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber.

How do i return disticnt records having count greater than 1

View 5 Replies View Related

Distinct Random Rows Using NewID()

May 26, 2008

I have 2 tables, Artists and Artworks.
I have a query:

SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()

This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,

Paolo

View 8 Replies View Related

How To Display All Columns Of Distinct Rows

Apr 17, 2014

I have the below working query

select distinct OrderNum from invoiceHistory where orderNum in (56387,57930,57933,57935)

I need to get all columns of the distinct ordernum as resultset. I am working to get the resultset of below.

select invoiceID,distinct orderNum,invoiceDate from invoiceHistory where orderNum in (56387,57930,57933,57935)

View 1 Replies View Related

Help With A Query (Selecting Distinct Rows As Well As How To Use NOW())

Jan 16, 2008

Hi All,

I'm a beginner in SQL and would like some help with writing a query that needs to:

a) Return the latest time that an event happened (along with the event), and also

b) Determine if this event occurred more than 30mins ago.



For example, Table EVENT consists of the following data:

EVENT DateTime,

A 16/1/08, 14:03:55

B 16/1/08, 14:30:27

A 16/1/08, 17:42:18



I would like the results for the first part of query to be:

EVENT DateTime,

A 16/1/08, 17:42:18

B 16/1/08, 14:30:27

I have tried creating a query based off this thread, but for some reason it kept complaining that the EVENT column in Table EVENT didn't exist.

For the part b), I have no clue as to what I should do apart from that I would need to use NOW().



Any help would be appreciated.



MonkeyMark

View 3 Replies View Related

Counting Distinct Records Of Column 1 With A Certain Value In Column 2

Feb 6, 2008

Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing


dbo_dbWafer_Slicing



WaferID
SawDate
SawRunNumber
   Pass





03-157.05    
1/8/2008 9:54:00 AM    
03-157
0




03-157.03
1/8/2008 9:53:00 AM    
03-157
-1




03-157.04
1/8/2008 9:53:00 AM    
03-157
0




03-157.02
1/8/2008 9:52:00 AM    
03-157
-1




03-157.01
1/8/2008 9:50:00 AM    
03-157
-1




03-165.06
1/4/2008 10:46:00 AM    
03-165
0




03-165.07
1/4/2008 10:46:00 AM    
03-165
0




03-165.04
1/4/2008 10:45:00 AM    
03-165
0




03-165.05
1/4/2008 10:45:00 AM    
03-165
0




03-165.02
1/4/2008 10:44:00 AM    
03-165
0




03-165.03
1/4/2008 10:44:00 AM    
03-165
0




03-165.01
1/4/2008 10:43:00 AM    
03-165
0





 So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help. 

View 6 Replies View Related

Selecting Distinct Top 3 Rows From Database Using Join

Jun 25, 2007

Hi guys,

Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;

"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"

Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?

Thanks.

View 6 Replies View Related

Select Distinct Returns Multiple Rows With Same Value

Apr 24, 2008

I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?

View 4 Replies View Related

Selecting Distinct Rows ?? From Over 10 Mill Records

Oct 27, 1998

hi, I have a table that contains 11,169,000 rows that was downloaded from the main frame. There are alot of duplicate records in that table. I ran a query select * from tbl.... it still running and running and running ... it never stoped.... what seems to be the problem.... There are no primary keys or index in that table...
so my question , how would I deal with such table ... I want to run certain reports from that table and it seems that all my attempts failed? anyone can help

View 5 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related

Retrieve Distinct Rows From Select Statement

Jan 10, 2014

Is it possible to retrieve Distinct rows from this Select Statement?

I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.

Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))

View 8 Replies View Related

Column In Query Results To Number Rows Sequentially

Apr 20, 2015

I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?

View 6 Replies View Related

Add Column To Existing Table With Large Number Of Rows

Dec 24, 2007

Hey Guys

i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all

whenever i do : alter table mytable add Updated_date datetime

it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit

can u plz advice on how to add this column. its urgent!!

thanks in advance

View 5 Replies View Related

TSQL - Avoid Duplicated Rows - Using Distinct / Group By

Sep 3, 2007

Hi guys,
need some help here please...

The code below shows 4 rows.
The first two rows are almost identical, but the two of them exists in the same table as different rows.
Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4
The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4.

I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error.
Thanks in advance,
Aldo.




Code Snippet
SELECT
RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID',
RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM',
JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY',
JURNALTRANSMOVES.SUF AS 'J.TOTAL',
STOCK.REMARKS AS 'S.REMARKS'

FROM
RECEIPTJURNALMATCH
INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID
LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID

WHERE
JURNALTRANSMOVES.ACCOUNTKEY IN ('123456')


Below the results:

R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS
89634 16702 123456 1155 עח: ;5752
89634 16703 123456 1155 עח: ;5752
89637 16702 123456 400 NULL
89639 16703 123456 155 NULL





View 9 Replies View Related







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