Selecting Distinct Top 3 rows from Database using Join

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?

6/25/2007 10:23:44 AM 29906 articles. 0 followers. Follow

6 Replies

Similar Articles

[PageSpeed] 55

P.S Sorry for the poor post formatting (lack of carriage returns). This forum isn't very compatible with Mac OS Safari and it's stripped them... :(
6/25/2007 10:57:41 AM

I don't know much about distinct but may UNION ALL solve your problem?

6/25/2007 1:29:39 PM
Thanks for your post. I've had a read up on UNION but can't quite see how it will translate to this particular case. Would you mind giving an example - how would my statement, something like;

"SELECT DISTINCT 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"

be translated to use  UNION?
6/25/2007 1:56:48 PM

UNION is used to combine two outputs in one table; like:

select * from table1


select * from table2

But I don't know if it work with only one select statement...


6/25/2007 3:11:40 PM
Thanks for this. I think I've just worked out how to do it though. This seems to work:

"SELECT TOP 3 * FROM feedTable WHERE ArticleID IN (SELECT ArticleID FROM Categories WHERE CategoryID=" + CategoryID + ")"
6/25/2007 4:24:16 PM

Good to hear that:)

happy programming..

6/25/2007 4:36:23 PM

Similar Artilces:

I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row.
 Hi, Let say I've 4 rows in a table. I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row. Thanks in advance Asghar Ali Mohammed Designer and Developer)Do not forget to "MARK AS ANSWER" on the post that helped you. If you can use Linq, you can use Skip., EdMicrosoft MVP - ASP/ASP.NET Hii dear..U can fire the Query select Top(3) from Table1 order by id desc.. "A conclusion is whe...

without using SQL, can i select distinct row??
as title, i want to select distinct row in a column. (I know how to do in writing a SQL, select distinct colname from table) but i don't want to hit the database again, so can i get it directly from dataView/DataReader/Datagrid???how? thank you for your help~~~~~~~~~~~ Take a look @ HOW TO: Implement a DataSet SELECT DISTINCT Helper Class in Visual C# .NETSushila Bowalekar PatelVisual ASP/ASP.NET MVP

Delete rows in SQL database using
Pls help: I have two tables: Tb and seller In the seller table: these are the following fields:ContactNo,StudentName,AdminNo,Serialcode In the tb table : these are the fields : Title, Author, Serialcode ***Each Title has its own unique Serialcode I wish to delete ContactNo, StudentName, AdminNo, Serialcode when i choose the particular Title. I not sure of the coding to delete rows....Pls help Coding: Dim objConnection As New SqlConnection(strConnection) Dim strSQL As String = "DELETE Seller.AminNo, Seller.ContactNo, Seller.StudentName, Seller.Serialcode FROM Sel...

retrieve selected data from SQL database using
I already watch the video in the express to learn about the data binding. But the problem that i face is when i want to retrieve certain data from the database. the video just show how to fill and update. who ever know the code to retrieve the selected row from the SQL database using 2005????? thanks do u want to retrive a particular cell or a particular row from the database?what control do you want to bind the retrived value to? thanks Imran NathaniLet the game begin!!Barcode image generation made easy.Writing an ActiveX Control in C#Generating Relational Data From XML D...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

uploading a image in to the sql server 2005 database using 3.5
i am currently developing a web project using need to upload a image file to the database using am using linq to interactwith the database. can some one help me in writing these codes. {i need to know how to get the image size and i need the image content type to upload image) please let me know what are the codes i should use to get the image size and content type. i have used the following code but no use                           &n...

Using 'Take' to Restrict Rows on LinqDataSource (Similar to 'Select Top ...' in SQL)
Dear All  I am using Linq to pull out records from an SQL server table called 'News' with the LinqDataSource as follows:<asp:LinqDataSource ID="NewsByDateDS" runat="server" ContextTypeName="NewsDataContext" GroupBy="newsAddedOn" Select="new (Key, It as Items)" TableName="News" OrderBy="newsAddedOn DESC" OrderGroupsBy="Key DESC">  I need to add an extra bit of functionality so that it only pulls out the top 10 records. In SQL, it would of been 'SELECT TOP 10 ...' and I u...

how to select first row or first 3 rows from my database with linq?
Hi, I am trying to retrieve the latest record from my database. have created a repeater and it works just fine selecting all the data from the db with: protected void Page_Load(object sender, EventArgs e) {MMDataContext db = new MMDataContext(strConnection);var qvote = from vote in db.Votes                 orderby vote.VoteDate descending                 select vote; Repeater1.DataSource = qvote;Repeater1.DataBind();} How to...

Using a row selection in a gridview to create database rows for another gridview
Hi there, I have 2 gridviews on a form, the first displays some template rows from a database table.  The second displays some user specific rows (with additional data added to the template info) from the same table. They both work fine for displaying the info.  Now what I want to do, is allow users to 'select' a template row on the first gridview, and have that action automatically: create a new user-specific row, with added fields from profile data etc. insert it into the table then refresh the second gridview in order to show the new added row. C...

How to transfer a GUID created using into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

How to use GridView row's Key selected for Delete as select parameter for another datasource
I'm not sure if the subject title makes sense, but it wasn't too easy to describe what I'm trying to do in one sentence! When a user clicks on the delete link in a GridView row, I want to run a stored proc, using the Key of the selected record as the parameter for the stored proc. I then want to programatically access the result of the stored proc (it'll be an integer value) for code-behind use. I wrote out a few steps to help me figure out what I need to do: 1) Get the value of PersonnelID (the DataKey) from the selected row in the GridView2) Set that Perso...

ORA-1722 using PB5.0.03 and SQL*NET
Hi there! Does anyone know how we can update a numeric datatype field from a datawindow using PB5.0.03, Oracle 7.3, SQL*Net, Windows 95 on the client side and Windows NT 4.1 on the server side? When we try to change the value of a numeric field, for example from 13 to 12, we get an ORA-1722 when we save the changes back to the database. We have tried the connection parameter disablebind=0 and it works fine, we don't get the error, but we need that parameter set to 1 because we are using stored procedures as data sources for some datawindows in our application. ...

Grid view rows operated using keyboard or arrow keys
Hi All..i need GridView rows - operated using keyboard and when a row is focused or selected, it should be it be in editable mode. when the focus moves to next row, the last row data, should get updated to database and new the data is to be displayed. The selected row must be now in edit mode. this to be done using arrow keys (Up and Down). It would be great help if you provide me a solution for this. Hi:   Please refer to following articles:   And foll...

Using PageTemplate to have a row used to add new rows to the datasource
I read in the Programming Microsoft ASP.NET 2.0 book that you can use the PagerTemplate to have an "extra" row used to add new rows to the datasource bound to the GridView.  Unfortuantely, it does not show how. I am trying to do this and I am having a problem where the Pager row is not showing up. I have defined a template:<PagerTemplate> <table width="100%"> <tr> <td><asp:LinkButton ID="btnAddFMEff" runat="server" Text="Add" CommandName="Add" /></td> <td style="width: 80%;"><asp:TextBox ID="txtNewEffect" runat="se...

Web resources about - Selecting Distinct Top 3 rows from Database using Join -

Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 ...
The " Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 " was a document published on ...

Voting Machine Caught on Camera Casting Ballot for Democrat when Selecting Republican - YouTube
**UPDATE.... I've heard many reports that this is "just a calibration issue." I will say this... when I went to page 2 and began voting for city ...

Selecting The Right Jeans
Fit, style, cut, colour and wash all factor into the pursuit for great denim.

Clock ticking on selecting successor to Leigh Clifford at Qantas
With Qantas on course to chalk up its largest annual profit since 2008, the question now is whether chairman Leigh Clifford is looking to hang ...

Why are the reserves selecting the team?
Backbenchers are the reserves of the parliamentary system. They are newcomers.

Jackie French: Why selecting the right place for your plants is important
The secret of growing anything easily is to choose the best spot for what you want to grow.

Netflix turns to pirates when selecting new shows
Imagine the surprise, then, when the popular video streaming site admitted that it actually turns to torrenters for inspiration. "With the purchase ...

Boston Marathon bombing trial: Judge, lawyers face challenges in selecting jurors - CTV News
As the second phase of jury selection began in the federal death penalty trial of Boston Marathon bombing suspect Dzhokhar Tsarnaev, it immediately ...

Selecting cabinet one of first orders of business for Trudeau
One of the first orders of business for any new prime minister is picking a cabinet. Who is likely to make the cut?

Agile Analysis, Self-Selecting Teams, TDD & BDD in Methods & Tools Winter 2014 issue
... for software developers, testers and project managers – has just published its Winter 2014 issue that discusses Agile Analysis, Self-Selecting ...

Resources last updated: 12/24/2015 2:41:48 AM