Solved

Stored procedure to return random records

Posted on 2003-11-14
17
497 Views
Last Modified: 2006-11-17
I have a table called photos that contains the following columns

recID, photoFilename, photoEventDate, photoDate


I have a stored procedure that returns 1 random record from each unique photoEventDate

Here is what I have so far


CREATE PROCEDURE dbo.randomFamily

AS
select photoEventDate,
(select top 1 i.photoFilename
from photos i
where i.photoEventDate = o.photoEventDate
order by newid() )
as photoFilename
from photos o
group by photoEventDate
GO


this works fine but I am not sure how to retrieve the other two fields (photoDate and recID)

thanks in advance.
0
Comment
Question by:CUTTHEMUSIC
  • 9
  • 8
17 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Something like this:

Select *
From photos
        Inner Join (select photoEventDate,
                                 (select top 1 i.photoFilename
                                  from photos i
                                  where i.photoEventDate = o.photoEventDate
                                  order by newid()) as photoFilename
                        from photos o
                        group by photoEventDate) X ON Photos.photoEventDate = X.photoEventDate

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Actually that should have been:

Select *
From photos
        Inner Join (select photoEventDate,
                                 (select top 1 i.photoFilename
                                  from photos i
                                  where i.photoEventDate = o.photoEventDate
                                  order by newid()) as photoFilename
                        from photos o
                        group by photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Also, please maintain your old open questions:

1 05/24/2002 100 Import MP3 into Flash MX  Open Macromedia Flash
2 02/24/2003 200 Read html using XMLHTTP  Open Web Development (COBOLdinosaur reminded you back in September, but you chose to ignore the reminder)

Thanks,
Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
Thanks Anthony, I will review them right after this.

Your solution works great except the photoEventDate column is returned twice
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Your solution works great except the photoEventDate column is returned twice <<
You are absolutely correct.  Also, I bet the photoFileName. Make this change:

Select photos.*
From photos
        Inner Join (select photoEventDate,
                                 (select top 1 i.photoFilename
                                  from photos i
                                  where i.photoEventDate = o.photoEventDate
                                  order by newid()) as photoFilename
                        from photos o
                        group by photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
Ok here is something else I just thought of

how can I add a WHERE clause that states
WEHRE photoDate BETWEEN '10/01/2003' AND '11/14/2003'

I also adjusted the points
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Not exactly sure what is the difference between photoDate and photoEventDate, but in any case it should be one of the following:

Select photos.*
From photos
        Inner Join (select photoEventDate,
                                 (select top 1 i.photoFilename
                                  from photos i
                                  where i.photoEventDate = o.photoEventDate
                                  order by newid()) as photoFilename
                        from photos o
                        Where photoDate Between '2003-10-01' And '2003-11-14'
                        group by photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename

Or:

Select photos.*
From photos
        Inner Join (select photoEventDate,
                                 (select top 1 i.photoFilename
                                  from photos i
                                  where i.photoEventDate = o.photoEventDate
                                  order by newid()) as photoFilename
                        from photos o
                        group by photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
Where photos.photoDate Between '2003-10-01' And '2003-11-14'

Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
You rock, is ther any way to sort them by photoDate??? or am I pushing it.

p.s. I just gave you all of my points, I might have to buy some more.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 175 total points
Comment Utility
>>is ther any way to sort them by photoDate??? or am I pushing it.<<

No problem, just add at the end of the select statement:

Order By photos.photoDate

>>I just gave you all of my points<<
You did not have to do that.  You may need them when you have a really difficult question <g>

Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
Ok one last thing, I promise


this works

SELECT *,
      MoreRecords = (SELECT COUNT(*)
                  FROM #TempItems TI
                  WHERE TI.ID > @LastRec
      )
      FROM #TempItems
      WHERE ID > @FirstRec AND ID < @LastRec

but I need to do it this way

@ParmTotalRecs INT OUTPUT
.......
MORE CODE IN HERE
......

SELECT *,
      @ParmTotalRecs = (SELECT COUNT(*)
                  FROM #TempItems TI
                  WHERE TI.ID > @LastRec
      )
      FROM #TempItems
      WHERE ID > @FirstRec AND ID < @LastRec
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Difficult to know what you "does not work" means.  

But I will hazard one guess:

You are trying to return a resultset as well as an output parameter.  

If this is not the case than change your code as follows:
Set @ParmTotalRecs = (SELECT COUNT(*)
                                    FROM #TempItems
                                   WHERE ID > @LastRec)

If it is not the case, then post your front-end code.

Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
I'm giving you the points even if you can't help me out with the last part.
Thanks again.
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
CREATE PROCEDURE sp_PagedItems222
(
      @Page INT,
      @RecsPerPage INT,
      @StartDate SMALLDATETIME,
      @EndDate SMALLDATETIME,
      @ParmTotalRecs INT OUTPUT
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Find out where we will start our records from
DECLARE @RecCount INT
SELECT @RecCount = @RecsPerPage * @Page + 1

--Create a temporary table
CREATE TABLE #TempItems
(
      ID INT IDENTITY,
      recID INT,
      photoFilename INT,
      photoEventDate VARCHAR(200),
      photoDate SMALLDATETIME
)

-- Insert the rows from tblItems into the temp. table

INSERT INTO #TempItems (recID, photoFilename, photoEventDate, photoDate)
      SELECT photos.*
      FROM photos
             INNER JOIN (SELECT photoEventDate,
                  (SELECT TOP 1 i.photoFilename
                  FROM photos i
                  WHERE i.photoEventDate = o.photoEventDate
                                      ORDER BY newid()) AS photoFilename
                  FROM photos o
                  GROUP BY photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
                  WHERE photos.photoDate BETWEEN @StartDate AND @EndDate
                  ORDER BY photos.photoDate DESC
-- Find out the first and last record we want
DECLARE @FirstRec INT
DECLARE @LastRec INT
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- THIS IS WHERE I AM HAVING PROBLEMS ###########

SELECT *,  SET @ParmTotalRecs = (
      SELECT COUNT(*)
      FROM #TempItems TI
              WHERE TI.ID > @LastRec
      )

FROM #TempItems
      WHERE ID > @FirstRec AND ID < @LastRec



DROP TABLE #TempItems
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Ok I see what you are doing now and it looks like you are trying to return a resultset as well as an output parameter.  While I can get that to work in SQL Query Analyzer, you may have problems using ADO in for example VB or ASP.  Hence my comment about the front-end code.

Rather than investing time giving you code that will only work in Query Analyzer, I need you to tell me how you plan to use this SP, so that I can suggest alternatives, such as forget about the output param and include another column for the count as you did earlier:
SELECT *,
     MoreRecords = (SELECT COUNT(*)
               FROM #TempItems TI
               WHERE TI.ID > @LastRec
     )
     FROM #TempItems
     WHERE ID > @FirstRec AND ID < @LastRec

Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
I'm writing everything is ASP.NET using VB and I can't figure out how to use the MoreRecords in my code

Here is my code, it is messy because I am using 2 or 3 different samples to do what I need.

Imports System
Imports System.Math
Imports System.Data
Imports System.Data.SqlClient
Public Class photos1
    Inherits System.Web.UI.Page
    Protected CurrentPageNumber As Integer
    Protected StartingPage As Integer
    Protected Reminder As Integer
    Protected Endpage As Integer
    Protected intPageSize As Integer = 500

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents DLPaging As System.Web.UI.WebControls.DataList
    Protected WithEvents CurrentPage As System.Web.UI.WebControls.Label
    Protected WithEvents TotalPages As System.Web.UI.WebControls.Label
    Protected WithEvents FirstPage As System.Web.UI.WebControls.LinkButton
    Protected WithEvents PreviousPage As System.Web.UI.WebControls.LinkButton
    Protected WithEvents NextPage As System.Web.UI.WebControls.LinkButton
    Protected WithEvents LastPage As System.Web.UI.WebControls.LinkButton
    Protected WithEvents TxtReminder As System.Web.UI.HtmlControls.HtmlInputHidden
    Protected WithEvents TxtPageNo As System.Web.UI.HtmlControls.HtmlInputHidden
    Protected WithEvents lbLast30Days As System.Web.UI.WebControls.LinkButton
    Protected WithEvents lbLast60Days As System.Web.UI.WebControls.LinkButton
    Protected WithEvents lbLast120Days As System.Web.UI.WebControls.LinkButton
    Protected WithEvents lbAll As System.Web.UI.WebControls.LinkButton
    Protected WithEvents lblTest As System.Web.UI.WebControls.Label

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        lbLast30Days.CssClass = "subMenuLink"
        lbLast60Days.CssClass = "subMenuLink"
        lbLast120Days.CssClass = "subMenuLink"
        lbAll.CssClass = "subMenuLink"
        If Not Page.IsPostBack Then
            StartingPage = intPageSize
            Endpage = intPageSize
            TxtPageNo.Value = 1
            CurrentPageNumber = 1
            BindData(30)
        End If
    End Sub
    Protected Sub VCRTypeNavigation_OnClick(ByVal Source As Object, ByVal E As CommandEventArgs)
        Reminder = CInt(TxtReminder.Value)
        Select Case E.CommandName
            Case "First"
                StartingPage = intPageSize
                CurrentPageNumber = 1
                TxtPageNo.Value = CurrentPageNumber
                Endpage = CurrentPageNumber * intPageSize
            Case "Last"
                If Reminder = 0 Then
                    StartingPage = intPageSize
                Else
                    StartingPage = Reminder
                End If
                CurrentPageNumber = CInt(TotalPages.Text)
                TxtPageNo.Value = CurrentPageNumber
                Endpage = CurrentPageNumber * intPageSize
            Case "Next"
                If CInt(CurrentPage.Text) + 1 = CInt(TotalPages.Text) Then
                    StartingPage = Reminder
                Else
                    StartingPage = intPageSize
                End If
                CurrentPageNumber = CInt(CurrentPage.Text) + 1
                TxtPageNo.Value = CurrentPageNumber
                Endpage = CurrentPageNumber * intPageSize
            Case "Prev"
                StartingPage = intPageSize
                CurrentPageNumber = CurrentPage.Text - 1
                TxtPageNo.Value = CInt(CurrentPageNumber)
                Endpage = CurrentPageNumber * intPageSize
        End Select
        BindData(30)
    End Sub
    Sub SubMenuItem_OnClick(ByVal Source As Object, ByVal E2 As CommandEventArgs)
        Reminder = CInt(TxtReminder.Value)
        StartingPage = intPageSize
        Endpage = intPageSize
        TxtPageNo.Value = 1
        CurrentPageNumber = 1
        BindData(E2.CommandName)
    End Sub
    Private Sub BindData(ByVal dblDateRange As Double)
        Dim myDataSet As New DataSet
        Dim MyDataAdapter As SqlDataAdapter
        Dim param As SqlParameter
        Dim ConString As String = ""

        'CONNECT TO DATABASE
        ConString = ConfigurationSettings.AppSettings("readDbConnString")
        MyDataAdapter = New SqlDataAdapter("sp_PagedItems222", ConString)
     
        With MyDataAdapter.SelectCommand
            .CommandType = CommandType.StoredProcedure
            'CREATE PARAMETERS
            .Parameters.Add(New SqlParameter("@Page", SqlDbType.Int))
            .Parameters.Add(New SqlParameter("@RecsPerPage", SqlDbType.Int))
            .Parameters.Add(New SqlParameter("@ParmTotalRecs", SqlDbType.Int))
            .Parameters.Add(New SqlParameter("@StartDate", SqlDbType.SmallDateTime))
            .Parameters.Add(New SqlParameter("@EndDate", SqlDbType.SmallDateTime))
            'SET PARAMETERS VALUES
            .Parameters("@Page").Value = CurrentPageNumber
            .Parameters("@RecsPerPage").Value = intPageSize
            .Parameters("@ParmTotalRecs").Direction = ParameterDirection.Output
            .Parameters("@StartDate").Value = Date.Now.AddDays(-dblDateRange).ToShortDateString()
            .Parameters("@EndDate").Value = Date.Now.AddDays(1).ToShortDateString()
        End With
        'FILL DATALIST
        MyDataAdapter.Fill(myDataSet)
        DLPaging.DataSource = myDataSet
        DLPaging.DataBind()

        Reminder = MyDataAdapter.SelectCommand.Parameters("@RecsPerPage").Value Mod intPageSize
        TxtReminder.Value = Reminder
        CurrentPageNumber = CInt(TxtPageNo.Value)
        CurrentPage.Text = TxtPageNo.Value

        If Not Page.IsPostBack Then
            TotalPages.Text = Ceiling(MyDataAdapter.SelectCommand.Parameters("@ParmTotalRecs").Value / intPageSize)
        End If
        'CLEANUP
        MyDataAdapter.Dispose()

        Select Case CurrentPageNumber
            Case 1
                PreviousPage.Enabled = "False"
                NextPage.Enabled = "True"
            Case CInt(TotalPages.Text)
                NextPage.Enabled = "False"
                PreviousPage.Enabled = "True"
            Case Else
                PreviousPage.Enabled = "True"
                NextPage.Enabled = "True"
        End Select
        lblTest.Text = dblDateRange
    End Sub

End Class
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Keep in mind that there used to be a problem in ADO classic with returning resultsets and output parameters:
http://support.microsoft.com/default.aspx?scid=kb;en-us;256234

So assuming that it is now fixed in ADO.NET you should be able to do the following (untested):

CREATE PROCEDURE sp_PagedItems222
(
     @Page INT,
     @RecsPerPage INT,
     @StartDate SMALLDATETIME,
     @EndDate SMALLDATETIME,
     @ParmTotalRecs INT OUTPUT
)
AS


-- Find out the first and last record we want
DECLARE @FirstRec INT
DECLARE @LastRec INT

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Find out where we will start our records from
--DECLARE @RecCount INT
--SELECT @RecCount = @RecsPerPage * @Page + 1

SET @FirstRec = (@Page - 1) * @RecsPerPage
SET @LastRec = (@Page * @RecsPerPage + 1)

--Create a temporary table
CREATE TABLE #TempItems
(
     ID INT IDENTITY,
     recID INT,
     photoFilename INT,
     photoEventDate VARCHAR(200),
     photoDate SMALLDATETIME
)

-- Insert the rows from tblItems into the temp. table

INSERT INTO #TempItems (recID, photoFilename, photoEventDate, photoDate)
     SELECT photos.*
     FROM photos
           INNER JOIN (SELECT photoEventDate,
               (SELECT TOP 1 i.photoFilename
               FROM photos i
               WHERE i.photoEventDate = o.photoEventDate
               ORDER BY newid()) AS photoFilename
               FROM photos o
               GROUP BY photoEventDate) X ON Photos.photoEventDate = X.photoEventDate and Photos.photoFilename = X.photoFilename
               WHERE photos.photoDate BETWEEN @StartDate AND @EndDate
               ORDER BY photos.photoDate DESC

-- THIS IS WHERE I AM HAVING PROBLEMS ###########
SET @ParmTotalRecs = @@ROWCOUNT - @LastRec

SELECT *
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

DROP TABLE #TempItems
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Comments:
1. I placed the DECLAREs for @FirstRec and @LastRec at the top.  Followed by the Set (as opposed to Select)
2. I commented out @RecCount as it is never used.
3. We can make use of @@RowCount to get the total number of rows added.  As an aside, it would be more efficient to only add the necessary rows (Include the Where clause in the Insert) and then use another Select statement to calculate the @ParmTotalRecs.
4. We can remove the @ParmTotalRecs from the final Select.

I suggest you first test this in Query Analyzer.  I am assuming you know how to do that, otherwise let me know.  When you are sure that it does what you need, then test it from your .NET code.  One thing you may want to double check is if that the order of the parameters is not important.

Anthony
0
 
LVL 2

Author Comment

by:CUTTHEMUSIC
Comment Utility
Hi Anthony,
   Thanks for your help again. I got everything working the way I want except now I am finding that I need to redesign my tables. I have posted another question and thought you may be able to help.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20799572.html


p.s. I purchased more points, thanks again.     :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

17 Experts available now in Live!

Get 1:1 Help Now